#DIV/0!

Christy

New Member
Joined
Feb 19, 2002
Messages
13
Hello!
I have a worksheet that makes calculations in cells based on the outcome of a calculation or input in another cell. If an input cell is empty it throws off the whole calculation cycle.
Here's a little more information:
Cells A5:D5 receive operator input of a quantity for items, if each item is ordered.
Cells A7:D7 divide the number in Cells A5:D5 by a number in A6:D6
Then those numbers in cells A7:D7 are added up in Cell G5.
If there is no amount put in any one or more of the cells A5:D5 (because no one ordered that item), it throws the whole calculation off and all you get is “#DIV/0!”
Looking through HELP, I believe it has something to do with “ISERROR” but, unfortunately, I am too ignorant to understand how to use it.
Here are the actual formulas I have for 1 column:
A5 = operator input (# of items ordered)
A7 = =ROUNDUP(A5/A6,0)
A46 = =ROUND((PRODUCT(A7,0.65)),1)
G5 = =ROUND(((PRODUCT(A6,A46)+PRODUCT(B6,B46)+PRODUCT(C6,C46)+PRODUCT(D6,D46))/1.72),1)
If A5 is blank, everything else reads "#DIV/0!"
Thank you so much for any help!
~Christy
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Here are two tips:

first, you can simplify some of your formulas. PRODUCT is great for multiplying lots of numbers in a range but * is often much easier.
ROUND((PRODUCT(A7,0.65)),1)
can just be
ROUND(A7*0.65,1)

ROUND(((PRODUCT(A6,A46)+PRODUCT(B6,B46)+PRODUCT(C6,C46)+PRODUCT(D6,D46))/1.72),1)
can just be
ROUND(((A6*A46)+(B6*B46)+(C6*C46)+(D6*D46))/1.72),1)
or even
ROUND(SUMPRODUCT(A6:D6,A46:D46)/1.72,1)

second, about the #DIV/0!.
It not clear why A5 would generate the error,
but if A6 is zero or blank, A7 winds up with an error because it divides by A6.

There are a lot of ways to handle this. One way is for A7 to be =IF(A6=0,0,A5/A6).

Hope this helps!
Sincerely,
Tim F-W
 
Upvote 0
I'll take a swag @ it - try this:

A7=if(iserror(ROUNDUP(A5/A6,0)),"n/m",ROUNDUP(A5/A6,0))

G5=if(iserror(ROUND(((PRODUCT(A6,A46)+PRODUCT(B6,B46)+PRODUCT(C6,C46)+PRODUCT(D6,D46))/1.72),1)),"nm",ROUND(((PRODUCT(A6,A46)+PRODUCT(B6,B46)+PRODUCT(C6,C46)+PRODUCT(D6,D46))/1.72),1))

you can use 0 instead of "nm"
 
Upvote 0
like tim i can't see why you'd be getting the #DIV/0! result because all your denominators are >0, but using the auditing toolbar may help. right click on a grey bit of the menu bar, select 'customise' at the bottom of the menu and on the toolbars tab check the auditing box. this toolbar allows you to trace precedent and dependent cells to formulas, as well as tracing errors, which is the button with the yellow diamond and the "!". handy for big sheets but i also find it a life saver when i have looked at something so many times i am looking straight past the error.
 
Upvote 0
Thank you so much! That was a very simple solution, Tim - you can't imagine how wonderful it was to find I only had to change ONE cell & all the rest worked right! Thank you ALL - I love all the tips!! ~Christy
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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