[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 364

# Excell error #DIV/0

I have designed an excell worksheet with columns A thru K.  My question refers to rows 10 thru 20 which have some simple calculations.
G10 has the formula:  H10-F10
G11 thru 19 has the same formula  (H11-F11, etc.)
J10 has the formula:  F10/H10  (The result is a percentage)
J11 thru 19 has the same formula  (F11/H11, etc.)
K10 has the formula:  D10*J10-E10
K11 thru 19 has the same formula  (D11*J11-E11, etc.)
I10 has the formula:  D10-H10
I11 thru I19 has the same formula  (D11-H11, etc.)

Row 20 contains totals for each column.
Example:  Cell G20  Formula: G10:G19

My problem is trying to get a running total in Cell K20. Formula: K11:K19.
Since the individual cells, K10 thru K19, have the formula D*J-E, and values have not been entered for all of those cells; I get an error: #DIV/0 in Cell K10 thru K19.

The problem is that Cell K20 will not give me a total unless all cells, K10 thru K19, have a value.

Example:  D10=100,000, J10=85%, E10=50,000, then K10=35,000.  (D10*J10-E10)

Since values have not been filled in for the other rows, K11 thru K19, I get the error #DIV/0 in Cell K20.

Is there any way to get rid of that error and show a value of Zero in cells K10 thru K19 until a value has been inputed in the other cells so that I will have a running total in Cell K20?
0
Miked062998
1 Solution

Commented:
Hi Miked.

-If you use division always test the cell you divide with

-so change the formula
-in cell J10 to =IF(VALUE(H<>0),J10/H10,0)
-and copy this down J11 through J19

-this will solve the #DIV/0 errors
-and will use 0 for cells not filled yet

HTH:O)Bruintje
0

Commented:
sorry my mistake

in cell J10 to =IF(VALUE(H10)<>0,J10/H10,0)
0

Commented:
bruin - take a look at this please:
http://www.experts-exchange.com/msoffice/Q.20289391.html
0

Commented:
Miked,

I like the following alternative for J10:
=IF(H10=0,0,F10/H10) then copy down the column as needed.

Hope this helps.
0

Commented:
Hi Miked
Excel has a worksheet function for error checking you can use: ISERROR(), which traps errors like :#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!

In your case you can use it like this:
in J10 enter
=IF(ISERROR(F10/H10);0;F10/H10)

:-)
regards
Peter
0

Author Commented:
Thank you all for your input.  They all were excellent.  Since bruintje responded first; I am awarding the points to him.

Thanks again
Mike
0

Commented: