Creating a basic excel formula

Posted on

QUESTION :

I’m no excel expert by any stretch and need some help with a formula from you helpful people.

As per the screenshot below, I’m wanting to cumulatively decrease the figure of $300,000 when a number is entered in the loss estimate field (I).

However, I only want a figure to show in the Aggregate column (K) when something is entered in the loss estimate column (I).

I hope this makes sense!

Thanks.

Excel Screen shot

ANSWER :

If I correctly understand your requirement, try this solution. I assume the output is not affected by the information present in Column J.

In this example cell K2 holds 300000 which is the starting amount.

In K3 put the following formula and drag it down.

=$K$2-SUM($I$3:I3) 

As you specify the loss estimates in I, the last value in K will be the cumulative reduction from earlier 300000 in K2.

However if the contents in J affect the output then a different solution would be needed.

enter image description here

To leave the unused cells in Col K blank, use the following formula in K3 and drag it down up to the indented cells.

=IF(ISBLANK(I3),"",$K$2-SUM($I$3:I3))

Just note that depending upon the regional settings for List Separator you may have to replace comma (,) with semicolon (;) for the formula to work correctly.

enter image description here

Leave a Reply

Your email address will not be published.