using (iserror) in if statement

sbugess

sbugess

New Member
Joined
Feb 20, 2006
Messages
27
I'm trying to supress a #div (divide by zero) error, and I'm using if(iserror(formula), and anything that has a an error, is working good. It brings back a 0 for me. What the problem is, anything that is false (not giving a divide by zero error), is returning a false statement. I need it to calculate the formula that I have in the statement, and return the value, not "false".

Any help on this, as this is the first time I have used the (iserror) in a formula?

Here is the if statement that I'm using...

=IF(ISERROR(IF('MSO PR'!Z14=0,0,(IF('MSO PR'!AB14<>0,'MSO PR'!AB14-'MSO PR'!Z14,0)))/'MSO PR'!Z14),0)

A suggestion was to include another if statement at the beginning, using the Iserror as the "true" statement, and the rest of the formula as the "false",

so it would be something like this -

=if('mso pr'!ab14=0,(IF(ISERROR(IF('MSO PR'!Z14=0,0,(IF('MSO PR'!AB14<>0,'MSO PR'!AB14-'MSO PR'!Z14,0)))/'MSO PR'!Z14),0)))

I know that I'm probably way off, but this is what I need it to do. Any help would be great!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Why not lose the ISERROR and just check for 0 like you have in the 2nd formula?

If it's 0 don't calculate, otherwise calculate.

=IF('mso pr'!AB14=0, "Hey that's division by zero!!!",('mso pr'!AB14-'mso pr'!Z14)/'mso pr'!Z14)
 
Upvote 0
You don't need ISERROR. Just check for the divisor being zero:

=IF('MSO PR'!Z14=0,0,IF('MSO PR'!AB14<>0,'MSO PR'!AB14-'MSO PR'!Z14,0)/'MSO PR'!Z14)
 
Upvote 0
Didn't know that they were linked, and I'm only subscribed to a few.

I will delete the (iserror) in the formula, and see if that works.
 
Upvote 0
Yes, it's working - thanks! I tried it earlier, and kept getting error statements all over the place!
 
Upvote 0
It's not that the sites/forums are linked.

If you want to find out more about cross-posting I'm sure you'll find FAQs on both sites and if you search the web.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question
Massive Nested IF Statement Help Needed
Replies
1
Views
168
Zutalores
Z
J
  • Solved
if isserror then look to next column for 0 if 0 then give me 0 not working
2
Replies
11
Views
163
Alex Blakenburg
A
K
  • Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
866
arthurbr
arthurbr
S
  • Question
INDEX and MATCH formula data limit?
Replies
1
Views
483
sspatriots
S
O
  • Question
Dividing a sumifs() statement by another sumifs() statement - Problem: Second sumifs() not recognizing valid ranges.
Replies
2
Views
280
jkpieterse
jkpieterse

Forum statistics

Threads
1,216,018
Messages
6,128,303
Members
449,439
Latest member
laurenwydo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top