Excell error #DIV/0

Posted on 2002-04-15
Last Modified: 2012-06-27
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?
Question by:Miked062998
LVL 44

Accepted Solution

bruintje earned 200 total points
ID: 6943062
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

LVL 44

Expert Comment

ID: 6943081
sorry my mistake

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

Expert Comment

ID: 6943147
bruin - take a look at this please:
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.


Expert Comment

ID: 6943685

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

Hope this helps.

Expert Comment

ID: 6944106
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


Author Comment

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

Thanks again
LVL 44

Expert Comment

ID: 6945566
glad i could help, thanks for the grade + points

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question