Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    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

  2. #2
    2 Star Lounger
    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

  3. #3
    Bronze Lounger
    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.

  4. #4
    2 Star Lounger
    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

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: If/or statement (2000)

    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

  6. #6
    Platinum Lounger
    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",(A2-A1)/A1)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Bronze Lounger
    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?

  8. #8
    Platinum Lounger
    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=""),"",(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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    3 Star Lounger
    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,(A2-A1)/A1,"N/A"))

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

    Aladin
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •