#DIV/0!

[Christy]

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

[Tim Francis-Wright]

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

[Gary_E]

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"

[anno]

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.

[Christy]

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