#Div/O! Errors

CurlingGal

New Member
Joined
Sep 26, 2004
Messages
13
I need some help getting rid of some DIV errors. They are occurring on multiple sheets. Can one of you suggest the best way I approach this? I cannot put a picture on the forum from this computer.

I do need this corrected, as now I have been asked to give someone a copy of the file, and I had actually been ignoring it, and delaying this posting.

I do OK in Excel, but as I no longer use it 40 hours a week, I have really forgotten a lot. Can you advise?

Thanks,

Rachel
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
CurlingGal

Could you give us some examples of the formulas that are producing the #DIV/0! errors?
 
Upvote 0
ISERROR will work, but you need to be aware of one thing if you use it. It will ignore ALL formula errors, not just DIV/0 errors. That has the potential to cause other problems, or "hide" other legitimate errors you may want to know about.

Usually, the best way to handle the situation is to address the formula that returns the error in the first place. If it truly is a simple problem of dividing by zero, it can be solved pretty easily.

For example, if your formula was:
=A1/B1
and the problem is that B1 may sometimes be 0 (or blank), use this intead:
=IF(B1,A1/B1,0)
 
Upvote 0
Here is an example:

=SUMIF($AX$3:$CB$3,J$2,$AX6:$CB6)/SUMIF($AX$3:$CB$3,J$2,$AX$5:$CB$5)

The only field that has any data is J$2 (It reads "H")

It is meant to determine the average of homework grades. I have 3 columns of similar formulas, designed to capture HW, CW and Test Averages.



=SUM(AX6:CB6)/D$5

This one is designed to give me the average of all grades.



=Senior!D6

This one reads the previous formula, on a different sheet.




=IF(G5="","",VLOOKUP(G5,'V Lookup'!$A$2:$B$6,2))

This one interprets the =Senior!D6 and turns it into a letter grade.




=SUM(E5,G5,I5,K5,N5)/MAX(1,COUNT(E5,G5,I5,K5,N5))

This one should be creating an average of 4 Marking Period + 1 Final grade, as they are made during the course of the school year.




=E5+G5+I5+K5+N5

This one is summing the 4 MP percentages + Final, as in our district they convert the percentages into points to determine whether a student passes for the year (300 "%" becomes 300 points, thus a pass (60%) for the year).



I am sure that the last several are driven by the second one I recorded for you. I know that I can't work on them until I resolve that one.
 
Upvote 0
The formulas work fine if there is data in the cells. I just would like to get rid of the #DIV/0 without having to create a conditional format for it, and just whiting it out.
 
Upvote 0
I just would like to get rid of the #DIV/0 without having to create a conditional format for it, and just whiting it out.
Who said anything about conditional formatting?

If you use patrickmuldoon's reply, that should do what you want, as if there is an error it will return nothing, otherwise it will return the average.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top