#DIV/0 with if statement

dorothy0831

New Member
Joined
Jul 19, 2006
Messages
11
Hello - I have a formula =IF(A2<=0,0,A2/B2) which is returning the error #DIV/0!. I tried iserror or iferror to resolve a similar situation in the past but it's not working for this.
Cell A2 is 0 and B2 is 4.5. Thoughts on how to resolve this issue?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You would only get that error if the value you are dividing by (B2) is 0.
But you say it is 4.5.

Is the value in B2 really a number, or is it a number entered as text?
You can tell by entering this formula anywhere and telling us what it returns:
=ISNUMBER(B2)
 
Upvote 0
Goodness, my apologize, I typed the formula backwards in the initial post. It should be =IF(B2<=0,0,B2/A2). Yes B2 is a number not text
 
Upvote 0
Then maybe your formula should really be:
=IF(A2<=0,0,B2/A2)

Most of the time, people use the IF to make sure the denominator is not zero, as the formula will work fine if the numerator is.
 
Upvote 0
What I am telling you is if B2 is 0, but A2 is not, the formula will return 0 without any errors. So there would be no reason to add anything special/extra (i.e. an IF statement for that case).
The formula:
=B2/A2
would return 0 in that case.

Now, if A2 was 0, you would be dividing by 0, which is not allowed mathematically, and you would get the #DIV/0 error.
That is when you would add an IF, and say IF A2 is 0, then return 0, else divide B2 by A2.

Does that make sense?
 
Upvote 0
Solution
Thank you Joe for pointing out what I was missing. I was so focused on the #DIV/0! error that I wasn't seeing the real issue. I have since changed the formula to adjust for no worked hours with a if/or statement.

Enjoy your day ahead and warm regards for your help.
 
Upvote 0
You are welcome.
Glad you were able to get it worked out.
:)
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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