Results 1 to 7 of 7
Thread: Using ISERROR

20100902, 14:04 #1
 Join Date
 Feb 2008
 Posts
 1,559
 Thanks
 139
 Thanked 12 Times in 12 Posts
I have the following formula =IF(C151<0,(B151+C151)/C151,B151/C1511)
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

20100902, 14:30 #2
 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/C1511)) should do it.
Andrew

20100902, 14:43 #3
 Join Date
 Dec 2003
 Location
 Burrton, KS, USA
 Posts
 833
 Thanks
 0
 Thanked 2 Times in 2 Posts

20100902, 20:47 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,952
 Thanks
 422
 Thanked 1,606 Times in 1,450 Posts
Howard,
To use ISERROR
IF(ISERROR(C151<0),0,if(c151<0,(B151+C151)/C151,B151/C1511))May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20100902, 21:26 #5
 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/C1511))
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/C1511)) still generates #DIV/0 if C151 is 0 OR Blank
=IF(ISERROR(1/C151),0,IF(C151<0,(B151+C151)/C151,B151/C1511)) 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 0Andrew

20100902, 23:08 #6
 Join Date
 Feb 2008
 Posts
 1,559
 Thanks
 139
 Thanked 12 Times in 12 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/C1511)) instead if ISERROR

20100903, 08:55 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,349
 Thanks
 4
 Thanked 228 Times in 210 Posts
Since you are using 2007, you can use IFERROR:
=IFERROR(IF(C151<0,(B151+C151)/C151,B151/C1511),0)
Regards,
Rory
Microsoft MVP  Excel