If/or statement (2000)

Topic

New Reply

Easy question, just too early in the morning: Column A contains data; in column B I would like to calculate the % difference between, say A1 and A2, A2 and A3, etc., but if any of the cells in column A are empty (some may be) or contain zeros, I would like the resultant % difference cell to show a “N/A”. For example, assume A1 is empty, and A2 contains 100. I would like the % difference cell (B2) to show “N/A”. Another example: Assume cell A1contains a zero, and A2 the same 100. I would like B2 to show “N/A”, instead of the “DIV/0”, or whatever it shows when one attempts to divide by zero.
Thanks in advance.
Jeff

Reply | Quote

Replies

Try =IF(ISERR(A2/A3),”N/A”,A2/A3*100)

or =IF(ISERR(A2/A3,”N/A”,A2/A3) if column B is formatted as a percentage.

Good Luck,
Stats

Reply | Quote

Thanks, Stats. Yor suggestion works OK except when, say, A2 is empty. The B2 cell then returns a -100.00%, and I want it to show a “N/A”, since there is no second value (i. e., A2) to compare to the first value (i. e., A1) to calculate the disfference.

Reply | Quote

Ooops, sorry about that, I missed the part where you wanted a 0 to read “N/A” as well.

Try =IF(ISERR(A6/A7*100),”N/A”,IF(A6/A7=0,”N/A”,A6/A7*100))

(Don’t forget to delete the “*100” part if col B is formatted as a percentage.)

Stats

Reply | Quote

Don’t think so-let me be more precise with an example. Assume the following: A1 is empty, A2 contains 100, A3 is empty, and A5 contains 67. The 5 difference calculation in B2 (without any modification) would be entered as follows: =(A2-A1)/A1. However, that would return a “#DIV/0”, when I want it to return a “N/A”. Further the % difference in B3 would return (again without modification) a -100.00%, when I want it to return a “N/A”, since there is no data in A3 to compare the difference of A3 and A2.
Hope this is more understandable!
Thanks,
Jeff

Reply | Quote

=IF(OR(A1=0,A2=0),”N/A”,(A2-A1)/A1)

Reply | Quote

Thanks, Pieterse, I didn’t know Excel interpreted a blank cell as a zero! Isn’t that what it is doing?

Reply | Quote

Yes, if referenced in a formula, an empty cell is evaluated as zero. But this works equally good:

=IF(OR(A1=””,A2=””),””,(A2-A1)/A1)

Or:

=IF(OR(ISBLANK(A1),ISBLANK(A2)),””,(A2-A1)/A1)

But both will not catch an actual value of 0 in the cells.

Reply | Quote

How about

=IF(COUNTBLANK(A1:A2),”N/A”,IF(A1,(A2-A1)/A1,”N/A”))

where you can replace “N/A” by just #N/A without quotes if so desired?

Aladin

Reply | Quote