#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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows
CurlingGal

Could you give us some examples of the formulas that are producing the #DIV/0! errors?
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
you want to use an iserror function

=if(iserror(yourformula),"",yourformula)

Hope this helps

Patrick
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,905
Office Version
  1. 365
Platform
  1. Windows
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)
 

CurlingGal

New Member
Joined
Sep 26, 2004
Messages
13

ADVERTISEMENT

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.
 

CurlingGal

New Member
Joined
Sep 26, 2004
Messages
13
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,905
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,401
Messages
5,547,740
Members
410,810
Latest member
thepinkbird
Top