Results 1 to 7 of 7

Thread: Using ISERROR

  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I have the following formula =IF(C151<0,(-B151+C151)/C151,B151/C151-1)

    However, If the value in C151 or B151 is zero, the formula returns #DIV/0

    I want to use the ISERROR formula that if this is the case, it mut return a zero as the result

    Your assistance will be most apreciated

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    =IF(OR(C151=0,B151=0),0,IF(C151<0,(-B151+C151)/C151,B151/C151-1)) should do it.
    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Dec 2003
    Location
    Burrton, KS, USA
    Posts
    833
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Andrew W View Post
    =IF(OR(C151=0,B151=0),0,IF(C151<0,(-B151+C151)/C151,B151/C151-1)) should do it.
    and if you want the cell to be blank instead of 0.

    =IF(OR(C151=0,B151=0),"",IF(C151<0,(-B151+C151)/C151,B151/C151-1))

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    To use ISERROR


    IF(ISERROR(C151<0),0,if(c151<0,(-B151+C151)/C151,B151/C151-1))
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I am not sure why you would want to use ISERROR here since it appears the only source of #DIV/0 is when C151=0 Or Blank
    I guess really you ought to also test for C151="" As Well

    =IF(OR(C151=0,C151="",B151=0),0,IF(C151<0,(-B151+C151)/C151,B151/C151-1))

    If you use ISERROR on C151 you do not allow for B151 = 0

    In this case =IF(ISERROR(C151<0),0,IF(C151<0,(-B151+C151)/C151,B151/C151-1)) still generates #DIV/0 if C151 is 0 OR Blank

    =IF(ISERROR(1/C151),0,IF(C151<0,(-B151+C151)/C151,B151/C151-1)) will generate 0 when C151 is 0

    but it will still not generate 0 when C151 is not 0 and B151 is 0

    Indeed if B151 =0 and C151 = 2 this generates -1 not 0 and I think the original question said


    IF B151 =0 OR C151 =0 it should be 0
    Andrew

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Guys

    Thanks for the help. I have used the formula =IF(OR(C151=0,C151="",B151=0),0,IF(C151<0,(-B151+C151)/C151,B151/C151-1)) instead if ISERROR

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Since you are using 2007, you can use IFERROR:
    =IFERROR(IF(C151<0,(-B151+C151)/C151,B151/C151-1),0)




    Regards,
    Rory

    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
  •