Question with Simple Formula

[Chris311]

I am trying to set up a couple of basic formulas to track changes. The B column will have the current number each day, the D column should track daily change (B2-B3 is what D3 lists) and the E column tracks cumulative change (referring to B2 as the starting point).

I've set it up so that it works up to the point I've inputed data in the B column. However, going down, B3 is blank so the formula lists change in D3 as the number in B2 (I assume it's treating the blank in B3 as 0, then displaying B2-0). I would like it to display that as N/A.

Currently, I have =IF(B2-B3>0,SUM(B2-B3), "N/A") as the formula for column D. How should I modify that so that if the cell in column B is blank, it lists the result as "N/A" instead of treating it as 0?

Thanks,

Chris

 

[VoG]

Try

=IF(AND(B3<>"",B2-B3>0),B2-B3,NA())

 

[Chris311]

Thanks for the fast response. Works fine if there is info in the B column (shows the change), but in the D column it's listing "#N/A" instead of "N/A" if the B cell is blank.

Any idea what the # is from? Thanks and sorry for my ignorance on this.

 

[Oeldere]

Try it this way

=if(isblank(b2),"#N/A",b2)

 

[VoG]

Thanks for the fast response. Works fine if there is info in the B column (shows the change), but in the D column it's listing "#N/A" instead of "N/A" if the B cell is blank.

Any idea what the # is from? Thanks and sorry for my ignorance on this.

Just replace NA() with "N/A" - note that that will give you a text value, not an Excel error value #N/A.

 

[Chris311]

Thanks, that seems to do it. If you don't mind explaining even more to me (as I try to learn to set up formulas beyond basic math), why is the "N/A" last? I'm not following the logic of the (AND(B3<>"",B2-B3>0) section. Is there a good reference on formulas for idiots I should look at?

Thanks again.

 

[VoG]

AND(B3<>"",B2-B3>0 is saying if both the following are true

B3 is not blank
B2-B3 is greater than 0

then apply the formula, else return N/A.

The general syntax of an IF statement is

=IF(X, return Y, return Z)