Addition of cells and plotting on graph

[will2k]

Evening Guys , Please could someone be so kind to assist with this excel issue im encountering i cannot figure out a way to make a formula work.

I basically have a monetary values entered in column "B" These can be zero , negative values or positive values

Column "F" is just a cumulative of them values as they are added in the cells in column "B"

what the problem is , i need the future cells in column "F" to remain at #N/A as my graph in sheet "COMB" plots from this and #N/A prevents forward plotting in the future.

At the moment it works fine providing the data entered in the B cells is a positive value , as soon as i enter a negative or zero value it dosent populate the F cell at all.
It waits for the next positive value to be entered into B and then it populates all the cells correctly in F that its missed .

Its hard to explain

TIA will

[Limor_OP]

Hi,

Try this in F2 and down:

=IF(SUM(B3:B$33)=0,NA(),SUM(B$3:B3))

[will2k]

Hi Belinda , Thanks for the reply , no it does not seem to work , i think it is impossible as sometimes the value in column B can be returned at zero , this then returns N/A in column f
im not sure how to do it ?

thanks will

[Limor_OP]

Try this in F3 and down:

=IF(AND(SUM(B3:B$33)=0,B3:B$33=0),NA(),SUM(B$3:B3))
This is an array formula, so you shuld press CTR+SHIFT+ENTER

[will2k]

Hi Belinda , Thanks for this latest effort , this now handles and works with negative values in column B . The only thing now is that if the value returned in column B is zero then the corresponding cell in column F
stays as #N/A.

thanks will

[will2k]

Hi Belinda , No actually this works i had not copied it down all cells, i have just tried it again and all works good !! , thanks for your help

[Limor_OP]

Great, glad it helps.
Let me correct myself with one thing - though I said it is an array formula this is a standard formula, no need of the curly brackets.

If your problem is solved, please go to Thread Tools and select Mark This Thread Solved
Thanks.