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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

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

##### MrExcel MVP, Junior Admin
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

##### MrExcel MVP, Junior Admin
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,026
Messages
5,856,930
Members
431,839
Latest member
Guest user

### 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?

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