#DIV/0! error message

Shelly68

Board Regular
Joined
Sep 12, 2006
Messages
73
I'm trying to calculate a formula that is trying to divide a cell that is ZERO, what is the formula I put in to make it appear as a Zero answer instead of the above (sorry that looks all muddled to me)
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
=IF(X1=0,0,formula)


where X1 is the cell that you are dividing by that may house a zero.

enter your formula in the formula place
 

Shelly68

Board Regular
Joined
Sep 12, 2006
Messages
73
sorry you just beat me, I figured I should probably put the formula in that I'm talking about (I did a search and saw that others had as well).

H23/(G23*$J$1)*G23

where H23 equals zero, (as a result of another formula)
G23 equals zero, (as a result of another formula)
and J1 equals 152

this is showing up as #DIV/0!

Does that make it easier to understand what I'm trying to say
 

xapie128

Board Regular
Joined
Aug 26, 2006
Messages
82
I'm trying to calculate a formula that is trying to divide a cell that is ZERO...
But later, you wrote,
H23/(G23*$J$1)*G23

where H23 equals zero, (as a result of another formula)
G23 equals zero, (as a result of another formula)
and J1 equals 152

this is showing up as #DIV/0!
The error you are getting is not caused by "trying to divide a cell that is ZERO" (that is, cell H23). That will return a 0, not an error. The error you are getting is because you are dividing by zero (that is, cell G23). It's okay if H23 is zero, but not that G23 is zero.

The suggestion that NBVC gave will work as far as not giving you an error (substitute G23 for X1), but realize that it violates mathematical rules. Dividing by zero isn't zero, and in fact is more like infinity (the smaller the number you divide by, the larger the answer).

This would be more correct: =IF(G23=0,"Undefined",H23/(G23*$J$1)*G23)
 

Shelly68

Board Regular
Joined
Sep 12, 2006
Messages
73

ADVERTISEMENT

Okay I tried that but it's now giving an answer of undefined, (which does not work either because I'm adding these elsewhere later on).

Is it not possible just to have it say 0??
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Okay I tried that but it's now giving an answer of undefined, (which does not work either because I'm adding these elsewhere later on).

Is it not possible just to have it say 0??

=IF(N(G23*$J$1),H23/(G23*$J$1)*G23,0)
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Okay I tried that but it's now giving an answer of undefined, (which does not work either because I'm adding these elsewhere later on).

Is it not possible just to have it say 0??

Yes it is:

=IF(G23=0,0,H23/(G23*$J$1)*G23)
 

Shelly68

Board Regular
Joined
Sep 12, 2006
Messages
73
Thank you everybody concerned for your help.

It works and makes life a heck of a lot easier,

thanks again!!!!
 

Forum statistics

Threads
1,141,681
Messages
5,707,792
Members
421,527
Latest member
Tamiwsw

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
Top