#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

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
Joined
May 28, 2005
Messages
55,027
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
63,968
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
63,968
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.
 
Master Excel Bundle

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.

Forum statistics

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

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
Top