How to add #N/A & a value?

[nginhong]

Dear expert,

I am creating a line chart to show the accumulated donation fund over a
period of 1 year (12 months) using data from column E.

My input would be Donation $ per month in column B and Number of visitors
per month in column C.

The problem now is I have to accumulate the donation fund month by month in
column E therefore I create a simple formula as follows-
In cell E2, = D2
In cell E3, = D3+E2
In cell E4, = D4+E3
In cell E5, = D5+E4

I am using the a formula to prevent #DIV/0! That will cause the line chart
to drop to zero if specific month has no donation fund.

When the formula in column D returns to #N/A the column E will stop adding
value as above formula. See table below staring from Aug:-
A B C D E
Jan 2,961 68 44 44
Feb 1,389 61 23 66
Mar 1,354 54 25 91
Apr 879 49 18 109
May 513 41 13 122
Jun 719 33 22 144
Jul 1,195 31 39 182
Aug 0 10 #N/A #N/A
Sep 0 16 #N/A #N/A
Oct 1,700 30 57 #N/A
Nov 0 8 #N/A #N/A
Dec 50 9 6 #N/A

Table:-
Column A = Month (Jan to Dec) (input)
Column B = Donation $ (input)
Column C = Number f visitors (input)
Column D = Donation per visitor (formula)
Column E = Accumulated donation fund month over month (formula)

How to make sure column E will continue the last value even the cell in
column D same row returns to #N/A?

That’s mean the formula should be able to add #N/A and a value then return
to a value. Example: #N/A + 10 = 10.

Thanks for your support!

BR//nginhong

 

[Bernard Liengme]

Replace =D3+E2 by =IF(ISNA(D3), E2, D3+E2)
best wishes

 

[nginhong]

Hi Bernard,

Thanks a lot! The formula is working fine.

I have another problem because if I preset the formula in column D and E for
12 months and now is only June '09, the formula you gave =IF(ISNA(D3), E2,
D3+E2) will continue to add the accumulated sum till Dec '09 so the chart
shows a constant horizontal line from June to Dec '09

How to make the formula return to #N/A if cell B2 and C2 are both blank or 0
(zero) so that the chart line will stop at June?

BR//nginhong

 

[Bernard Liengme]

=IF(AND(B2=0,C2=0),NA(), IF(ISNA(D3), E2, D3+E2))
This is called a nested IF
A blank cell will be considered equal to zero by this formula
best wishes

 

[nginhong]

Hi Bernard,

Thanks again!

I changed the formula to =IF(AND(B3=0,C3=0),NA(), IF(ISNA(D3), E2, D3+E2))
and it's working fine.

BR//nginhong