# #Div/O! Errors

#### CurlingGal

##### New Member
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

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
CurlingGal

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

#### patrickmuldoon99

##### Active Member
you want to use an iserror function

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

Hope this helps

Patrick

#### Joe4

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

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
BTW..thanks again..

#### CurlingGal

##### New Member
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

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.

Replies
1
Views
41
Replies
1
Views
85
Replies
99
Views
2K
Replies
5
Views
109
Replies
4
Views
360