Results 1 to 9 of 9
Thread: If/or statement (2000)

20020626, 09:58 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
If/or statement (2000)
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

20020626, 10:18 #2
 Join Date
 Jan 2002
 Location
 Trenton, Ontario
 Posts
 175
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If/or statement (2000)
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

20020626, 10:25 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: If/or statement (2000)
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.

20020626, 10:25 #4
 Join Date
 Jan 2002
 Location
 Trenton, Ontario
 Posts
 175
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If/or statement (2000)
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

20020626, 10:34 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: If/or statement (2000)
Don't think solet 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: =(A2A1)/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

20020626, 10:50 #6
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If/or statement (2000)
=IF(OR(A1=0,A2=0),"N/A",(A2A1)/A1)
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020626, 19:01 #7
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: If/or statement (2000)
Thanks, Pieterse, I didn't know Excel interpreted a blank cell as a zero! Isn't that what it is doing?

20020627, 05:38 #8
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If/or statement (2000)
Yes, if referenced in a formula, an empty cell is evaluated as zero. But this works equally good:
=IF(OR(A1="",A2=""),"",(A2A1)/A1)
Or:
=IF(OR(ISBLANK(A1),ISBLANK(A2)),"",(A2A1)/A1)
But both will not catch an actual value of 0 in the cells.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020627, 20:23 #9
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If/or statement (2000)
How about
=IF(COUNTBLANK(A1:A2),"N/A",IF(A1,(A2A1)/A1,"N/A"))
where you can replace "N/A" by just #N/A without quotes if so desired?
AladinMicrosoft MVP  Excel