formula to make data plot on graph correctly

[will2k]

morning all , i have attached a few relevant columns of a sheet i need help with please.

column B is a monetary daily P&L value
column F is basically an accumulative values of column B

The problem i have is i run a graph (sheet COMB) from the data in column F so i need it to not plot the future
days when not populated hence i have them as #N/A while not populated

what im trying to do is
keep column F accumulating the data that is returned in column B but whilst keeping the future cells at #N/A to make it plot correctly on my graph

the formula that is in column F now is what i have been trying myself and it does not work

thanks in advance
will

[GlennUK]

In what way does it not work?

[Kaper]

Try such formula in F3 (and copy down):

Please Login or Register  to view this content.

Note I used NA() formula, not just #N/A and as you have some days without input the above formula calculates cumulative sum every time from the beginning. So in row 10 it will read

Please Login or Register  to view this content.

[will2k]

Hi Kaper , Thanks for your reply , when there is a 0.00 entered in column b i need the same cell in column F to display the accumulative number (not #N/A) see attached image were arrowed i need this to be 90.0


thanks will

[will2k]

Hi Glenn , The reply to kaper may explain it more clearly , dates in the future in column F need to stay as #N/A to prevent forward plotting on graph , but when a zero return is populated in column B the calculation in column F still needs to be carried on ( see picture i attached) .
thanks will

[Kaper]

Then probably

Please Login or Register  to view this content.

will be enough.
Or may be

Please Login or Register  to view this content.

if you want to stop showing cumulative values after last input in column B (I'm refering to this part of first post: "but whilst keeping the future cells at #N/A to make it plot correctly on my graph ")

[will2k]

thanks Kaper ,

=IF(COUNTIF(B3:B$33,">0")>0,SUM(B$3:B3),NA()) works perfectly , many thanks for your assistance


regards Will

[Kaper]

Glad to hear so
And thanks for marking thread solved and for reputation point