Free Microsoft Excel 2013 Quick Reference

divide by zero error...

I am creating a 'weekly average spreadsheet' (Excel 2007) for a teacher. It has a column for each student's name and his test grade for each day (M-F). The last column gives his weekly average.

She also needs an average for all the students' for the week (vertically). My formula is correct, i.e., =AVERAGE(H2:H11). However, I'm getting a divide by zero error because I have some blank rows above my formula (so she can add new students' names as they come.

How can I get rid of the divide by zero error so she can see the running averages as she inputs grades (but still leaving empty rows for new names)?

Thanks so much for your help!


Post your answer or comment

comments powered by Disqus
Hi there, Is it possible to replace 'Divide by Zero Error' with just a Zero?

See a small section of what I'm trying to do.

Sorry I can't add the full spreadsheet it huge over 4 MB. way too big for this forum.

Thanks

Gary Hunt

I have a database that requires me to find the average of some data over several columns, however if one of the values I have to divide by is a zero I get a 'Can't Divide By Zero Error' is there any way to override this ? So that if a Divide by Zero Occurs, instead of returning a 'Can't Divide By Zero Error' it just replaces it with a Zero instead ?

Thanks for any help offered in advance.

Gary Hunt

Hello

In the attached sheet, I have formula in cell b8 to find minimum of range ("b4:f4"). Since cell d4 has divide by zero error, not able to get the minimum. I think we can use iserror function to sense divide by zero error. Is there a way to write a formula in cell b8 to find the minimum of range ("b4:f4") that will avoid divide by zero cells in range("b4"f4"), without macros?
Appreciate your help.

I want to be able to multiply the values in 2 cells and divide by the values in a third cell. The problem being, that I want to be able to preform this operation sometimes 1, 2, 3, or 4 times and then use the sum of how ever many of these functions I need to use on a given operation. If I only use 2 and enter
nothing in the other cells, the divide by zero error won't allow me to gain a value in the cell with the sum function.
Please help,
Skip

I want a cell to be blank if the sum is Zero. I am getting a divided by zero error, I tried this formula with no luck:

=IF(SUM(C6/D6)=0,"",SUM(C6/D6))

I appreciate any help.

Thanks,
Nick

I get a "divide by zero" error in some cells. Can I have it display zero instead of the #DIV/0 error message?

I have two worksheets, one containing raw data, and the other a pivot table averaging that data. In the first sheet, I have the candidates name in order going down the rows(1-?), in the columns(A2-?), I have 20 questions w/their scores and at the end an average of those scores. The problem is that not everyone has taken the test, and the average cell only shows a divide by zero error(#DIV/0!). This is screwing up the average display in the pivot table in the next worksheet. I was told not to get rid of the candidates that have not taken the test, so how do I get around this? Is there someting I can add to the pivot table so that it ignors error rows?

Thanks,
Greg

I'm trying to write a formula to calculate the percentage of two cells. The
simplified formula reads:
=B12/(B12 + H12)

The problem is when H12 and B12 both = zero. Zero divided by zero = divide
by zero error.

The desired result, display 0%

I looked at the online help, and it gives me a sample formula, but for the
life of me, i just can't seem to get it to work out with 'my desired
result'...

help... please...

I have a worksheet that I am using as a template to calculate training cost. I have set up formulas that will calculate number of groups for instance based in the user input of number of trainees and number of trainees per instructor. I would like to prevent the display of the divide by zero errors and value errors for when some of the cells are blank. I have tried using the conditional formatting and changing the font color to white but this only works if the entire section of the column has errors. As soon as I input numbers into a couple of rows, the error text returns. Is there any other way to get rid of these? I have attached a sample of my worksheet.

I have the following formula where column CL remains blank until I populate it, however, the following formula gives a divide by zero error with blank cells.

The other issues is that column C may not always contain the text CPHA

Any help would be much appreciated! (Pulling my hair out on this one!)

Please help with the following formula,
E20 and E22 , when left blank = value of 0, Therefore " Divide by Zero
Error " condition occure affecting the rest of the form.
example =sumif(E20,E22)=<0*(E2/E22)*167

I've got an annual grocery list that I've put together that picks the best price out of all the prices entered (currently only Sams and our neighborhood loss leader store).

The forumula looks like this, and it's on every line.


	VB:
	
=SUMIF(C3:D3,"="&MIN(C3:D3))/COUNTIF(C3:D3,"="&MIN(C3:D3))*B3 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem I'm running into is that it chokes if I haven't entered the price for a place.
I.E. if I have no price for shoes at the Grocery store (not sold there) and I haven't put in a price for Sams then it tries to divide by zero and gives an error.
I'd like to have all of the zeroes (or blank cells) ignored. Is that possible?

Thanks!

McTester

I have a formual that I auto..

	VB:
	
=C2/A2 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But if I get a Zero or if I divide by zero I get and error. The result from C2/A2 is summed in a nother column, and i cant get it to sum because i get the error "#DIV/0!". I have tried everything, but i do not know how to get rid of it. Thanks for your help.

formatting goes to pot here so I cant represent the tables as I'd like,
but hopefully you get the gist! User entry actual figure is only
entered obviously when there is a figure so alot of the time will be
blank or zero the fixed value target is always on the sheet and the
percentage is basically how much percent of the target has been
acheived which is a pct value.

Is there anyway of substituting a zero in when you have a pct divided
by zero as normally it just comes up with the error message

ie

USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5 B1/A1%

There is no user entry until a value needs to be input and until that
point the pct always shows up with the error message, I'd prefer a
zero, cosmetics I know but would be curious to know.

Currently get:

USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5
NULLyadayadacannotdividebyzeroerrormsg

Would prefer:
USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5
0%

Obviously as soon as a user entry does appear then the formula should
alter as usual and display standard percentage

USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5 5 100%

--
dave99
------------------------------------------------------------------------
dave99's Profile: http://www.excelforum.com/member.php...o&userid=30221
View this thread: http://www.excelforum.com/showthread...hreadid=498993

formatting goes to pot here so I cant represent the tables as I'd like, but hopefully you get the gist! User entry actual figure is only entered obviously when there is a figure so alot of the time will be blank or zero the fixed value target is always on the sheet and the percentage is basically how much percent of the target has been acheived which is a pct value.

Is there anyway of substituting a zero in when you have a pct divided by zero as normally it just comes up with the error message

ie

User Entry Actual Figure Fixed Value Target Percentage
5 B1/A1%

There is no user entry until a value needs to be input and until that point the pct always shows up with the error message, I'd prefer a zero, cosmetics I know but would be curious to know.

Currently get:

User Entry Actual Figure Fixed Value Target Percentage
5 NULLyadayadacannotdividebyzeroerrormsg

Would prefer:
User Entry Actual Figure Fixed Value Target Percentage
5 0%

Obviously as soon as a user entry does appear then the formula should alter as usual and display standard percentage

User Entry Actual Figure Fixed Value Target Percentage
5 5 100%

Hello:

I have lots of #DIV/0!.
This is the display you get when you have value divide by zero,
I want to eliminate to display this error when i open the sheet.
Basically when i go to the sheet with #DIV/0! then it needs to show blank with the formula
in the cell.

Is there a solution for this.

Thanks and looking forward for help.

RM

Hi guys,

I have an irritating problem. I am trying to do a seemingly simple division of 2 numbers, both declared as 'Long' variables. The first variable 'Itotal' represents the number of rows that contain a certain a certain piece of information.

Dim Itotal As Long
   
Itotal = WorksheetFunction.CountIf(Range("M3:M50"), M04Input.Index)

I then delete some of these rows with some other code and then find how many rows are left containing the same piece of information:

Dim Ileft As Long

Ileft = WorksheetFunction.CountIf(Range("M3:M50"), M04Input.Index)
Finally I want to calculate the number of rows that are left as a proportion of the originals.

Dim Icomplete
As Long
Icomplete = (Ileft / Itotal)
I've inserted several debug statements in the code and the 'Itotal' and 'Ileft' variables seem to be calculated ok. In the run I've just done 'Ileft' was 14 and 'Itotal' was 15. However I get an error statement relating to the final part of the code telling me that I'm trying to divide by zero. Clearly I'm not so why is this going wrong?

Any help would be appreciated!

Thanks,

Christian

Hi

When using formulas the following message appears if you try to divide by zero:

#DIV/0!

Is there anyway of fixing it so that if the denominator is zero, the equation will equal zero
ie.

Range("A3") = "=10/$C$3"
if C3 contained a zero then I would like the contents of A3 to contain a zero.

If anyone knows this one they are a genius!
Thanks
Mike

Hi guys,

So everyone knows that when you have a number divided by zero, the result is undefined, and I'm having trouble working around that.

This is the function I wrote:


	VB:
	
 Single 
     
    calculation = conc / factor 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Looks like a good function. In fact, it works for all concentration values and factor values as long as the factor is not zero, otherwise the value "calculation" would be undefined.

I was thinking of writing an "if" statement testing the value of the "factor" before calculating the function. If the value of the "factor" is zero, I'd like the value of calculation just to show up as zero.

Any ideas?

Any help would be greatly, greatly, greatly appreciated! =D

I know this is as trivial as a question can be:

in column E i need to add a formula:


	VB:
	
=($C2 - $B2)/$C2 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And i would be extending it to the rows below.
Now, obviously, if C2 contains a 0, it gives a divide by 0 error. How to modify the formula which makes it valid only if C2!=0(not equal to 0)

Thanks,
Ken

I'm trying to get the % increase but when its goes from 0 to "xx value", I get the division by zero error.

For example:

2006 Sales: 0
2007 Sales: 10,000

It would show the correct % increase in sales.

Thanks in advance for any assistance!

anything that is divide by zero will get #DIV/0!. how can I show the result
ias zero insted?

Hi there,

I have this equation
Private Sub Cal_btn_Click()

ThreePh_FC.Value = ((MVA_Base * 1000000) / ((3 ^ 0.5) * kV_Base * 1000)) / ((XCab_Pos * (Length / 10000000) / (((kV_Base *
1000) ^ 2) / (MVA_Base * 1000000))) + _
((MVA_Base * 1000000) / ((3 ^ 0.5) * kV_Base * 1000 * If_3ph)))

'OnePh_FC = ((3 * MVA_Base * 1000000) / _
((3 ^ 0.5) * kV_Base * 1000)) / Ztotal_1Ph

End Sub
My Question ;

1. If the user enter kV_Base = 0, then error msg came out. Division by zero. How to prevent the user to key in kV_Base = 0

2. And also, how to prevent user to key-in alphabet?

3 If there is any error (e.g above error) occur, is it possible to give the user an alarm or locked the button instead of return to the debug/VB error msg

4. If you refer to my attachment/code, at (Length / 10000000).If i use my calculator, there is no need to include 10000000. Initially it suppose to be 'length' only. But the answer need to devide by 10000000. Then I insert 10000000 to get the same answer as my calculator.

5. How to name an arry. I.e Colum A refer to People name (eg Smith, Dean, John) and the row 1 refer to Monday till row 7 refer to Sunday.

Thanks guys for reading my questions!!!

I have this script that runs ok as lat long are not the same. When they are the same I get run time error 11 can't divide by zero. Is there a way to trap this error. Any help is appreciated.

Private Function ACos(X As Double) As
Double
ACos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

Sub CalculateDistance()
Application.ScreenUpdating = False
    Set ws1 = Sheets("Input_Data")
    Set ws2 = Sheets("Output")
    Set ws3 = Sheets("Process_Data")
    Set ws4 = Sheets("Start")
    Set ws5 = Sheets("dyn RN")
    Dim D As Single
    Dim L1 As Single
    Dim L2 As Single
    Dim G1 As Single
    Dim G2 As Single
    Dim Name1, Nam2 As String
    Dim k, i, Dist As Long
    Const pi = 3.1415926

    i = Range("A1").End(xlDown).Row
    
    For k = i To 2 Step -1
    Lat1 = Format(ThisWorkbook.Worksheets("Process_Data").Cells(k, 3))
    Long1 = Format(ThisWorkbook.Worksheets("Process_Data").Cells(k, 4))
    Lat2 = Format(ThisWorkbook.Worksheets("Process_Data").Cells(k, 5))
    Long2 = Format(ThisWorkbook.Worksheets("Process_Data").Cells(k, 6))
      
    '-------------------Match Source and Target----------------------------
    'If source and Target are equal then their distance is zero.
        Name1 = ws3.Cells(k, 1)
        Name2 = ws3.Cells(k, 2)
        If Left(Name1, 7) = Left(Name2, 7) Then
            ws3.Cells(k, 7) = 0
            GoTo line1
        Else
            L1 = (90 - Lat1) * (pi / 180)
            L2 = (90 - Lat2) * (pi / 180)
            G1 = Long1 * (pi / 180)
            G2 = Long2 * (pi / 180)

       End If

D = ACos((Cos(L1) * Cos(L2) + Sin(L1) * Sin(L2) * Cos(G1 - G2)))
Dist = D * 3963

ThisWorkbook.Worksheets("Process_Data").Cells(k, 7) = Format(Dist)
line1:
Next k

End Sub



No luck finding an answer? You could always try Google.