[Webinar] Streamline your web hosting managementRegister Today

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

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
Asked:
Miked062998
1 Solution
 
bruintjeCommented:
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
 
bruintjeCommented:
sorry my mistake

in cell J10 to =IF(VALUE(H10)<>0,J10/H10,0)
0
 
starlCommented:
bruin - take a look at this please:
http://www.experts-exchange.com/msoffice/Q.20289391.html
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
bkpchs237Commented:
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
 
forsbomCommented:
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
 
Miked062998Author 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
 
bruintjeCommented:
glad i could help, thanks for the grade + points
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now