Ignoring #DIV/0! Errors in Formulas

[Tom L.]

Hello folks!

I can't seem to find any information on ignoring div/0 errors when using formulas. Currently I have a lot of formulas that read from cells which currently have a div/0 error due to another formula. I'd rather make some changes to the formula reading the div/0 errors than change the formulas themselves to change any div/0 errors (using the countif tips given to me on my last post - thanks for the help, once again).

How do you make a formula ignore div/0 errors? If that doesn't work, does that mean I need to change all of my formulas which result in div/0 errors?

Any help would be greatly appreciated.

Examples:
Formula 1: AVERAGE(A1:A50) - results in #DIV/0!
Formula 2: AVERAGE(Formula 1, someothervalue)

 

[Aladin Akyurek]

Hello folks!

I can't seem to find any information on ignoring div/0 errors when using formulas. Currently I have a lot of formulas that read from cells which currently have a div/0 error due to another formula. I'd rather make some changes to the formula reading the div/0 errors than change the formulas themselves to change any div/0 errors (using the countif tips given to me on my last post - thanks for the help, once again).

How do you make a formula ignore div/0 errors? If that doesn't work, does that mean I need to change all of my formulas which result in div/0 errors?

Any help would be greatly appreciated.

Examples:
Formula 1: AVERAGE(A1:A50) - results in #DIV/0!
Formula 2: AVERAGE(Formula 1, someothervalue)

If you don't want to end up with a slow spreadsheet, you can better get rid of the formulas that produce such errors. That said:

=SUMIF(A1:A50,"<>#DIV/0!")/MAX(1,COUNTIF(A1:A50,"<>#DIV/0!")

 

[chrisxxyyzz]

Try this formula

=IF(ERROR.TYPE(Formula 1)=2,0,Formula 1)

 

[Aladin Akyurek]

Try this formula

=IF(ERROR.TYPE(Formula 1)=2,0,Formula 1)

Did you test that? Try on:

Formula 1: B1/C1

B1: 4

C1: 2

What do you get with...

=IF(ERROR.TYPE(B1/C1)=2,0,B1/C1)

 

[chrisxxyyzz]

Dear Aladin,

Good point. I did get the zero I was after and that is where I stopped.

 

[Tom L.]

If you don't want to end up with a slow spreadsheet, you can better get rid of the formulas that produce such errors. That said:

=SUMIF(A1:A50,"<>#DIV/0!")/MAX(1,COUNTIF(A1:A50,"<>#DIV/0!")

Thanks for the suggestion. Unfortunately it does not work if some of the values have a blank cell.

Sorry, I didn't explain this before. The issue that I'm trying to work out is that there are a variable number of incidents that work into the average. Sometimes a value will intentionally equal a zero, but if the cell is left blank I want the formula to ignore it in the way that Excel does it by default.

Example:

Line 1: 2, 5, 2, 3, 2, 0. Average ~= 2.33; no change with formula.
Line 2: 1, 4, 3, 5, 2. Average = 3; Average w/ Formula = 2.5

Does that help explain it better? I have a formula that will probably do this, but I'd really prefer not having to change all of those formulas across 250+ different workbooks with 12 individual sheets each. With 4 instances of the formula on each sheet, that turns out to 12,000 individual formula edits (yeah, I know...I'm working on consolidating it). As a note, it's not a very large spreadsheet so I won't have to worry too much about how slow it goes.

The formula I currently have is what I received as a suggestion from my last thread (used on a different, single workbook):

=IF(COUNTIF(Q2:Q61,"<>0")<>0,SUM(Q2:Q61)/COUNTIF(Q2:Q61,"<>0"),0)

 

[Aladin Akyurek]

If you don't want to end up with a slow spreadsheet, you can better get rid of the formulas that produce such errors. That said:

=SUMIF(A1:A50,"<>#DIV/0!")/MAX(1,COUNTIF(A1:A50,"<>#DIV/0!")

Thanks for the suggestion. Unfortunately it does not work if some of the values have a blank cell.

Sorry, I didn't explain this before. The issue that I'm trying to work out is that there are a variable number of incidents that work into the average. Sometimes a value will intentionally equal a zero, but if the cell is left blank I want the formula to ignore it in the way that Excel does it by default.

Example:

Line 1: 2, 5, 2, 3, 2, 0. Average ~= 2.33; no change with formula.
Line 2: 1, 4, 3, 5, 2. Average = 3; Average w/ Formula = 2.5

Does that help explain it better? I have a formula that will probably do this, but I'd really prefer not having to change all of those formulas across 250+ different workbooks with 12 individual sheets each. With 4 instances of the formula on each sheet, that turns out to 12,000 individual formula edits (yeah, I know...I'm working on consolidating it). As a note, it's not a very large spreadsheet so I won't have to worry too much about how slow it goes.

The formula I currently have is what I received as a suggestion from my last thread (used on a different, single workbook):

=IF(COUNTIF(Q2:Q61,"<>0")<>0,SUM(Q2:Q61)/COUNTIF(Q2:Q61,"<>0"),0)

It seems you don't want 0's included...

=SUMIF(A1:A50,"<>#DIV/0!")/MAX(1,COUNT(A1:A50)-COUNTIF(A1:A50,0))

 

[Justinlabenne]

If you just want to hide the #DIV/0's, I made a utility I use at report time to hide them all. It allows for the formulas to be reversed back to their original state also. If you use it, have a backup in case it's not what you expected.
Download it here: Error Remover: http://jlxl.net/Excel/downloads.html

Otherwise stick to Aladin's methods, he knows best.

 

[Tom L.]

It seems you don't want 0's included...

=SUMIF(A1:A50,"<>#DIV/0!")/MAX(1,COUNT(A1:A50)-COUNTIF(A1:A50,0))

Oops...yeah, I had a complete brain spasm on that one. The formula I'm using above excludes zeroes intentionally, but in this particular incident I *do* want to include zeroes. So the formula I have won't work either. Sorry - I'll try to make more sense and get some caffeine in me.

If I'm reading the formula right, it's going to first take the sum of the cells that do not equal (greater than or less than) div/0, then divide that by the largest value of either '1' or the total number of cells that are not zero, right? A variation of that might work, but I want it to ignore blank cells rather than zeroes. Again, I didn't state that very clearly...sorry about that. As a note, I tried replacing the zero in the formula with "", but that didn't make a difference in the formula results and zeroes are still ignored (probably reads as a zero).

If you just want to hide the #DIV/0's, I made a utility I use at report time to hide them all. It allows for the formulas to be reversed back to their original state also. If you use it, have a backup in case it's not what you expected.
Download it here: Error Remover: http://jlxl.net/Excel/downloads.html

Otherwise stick to Aladin's methods, he knows best.

Thanks, I might give that a try. I don't know if hiding them is exactly what I'm going for and I don't think I want to remove *all* error messages, but it's something I will check out. I have a blank template for the workbook that I can test it on. Not today, though - I'm apt to have another spasm and erase all my work or something. Heh...

Thanks for the help thus far. If anyone else has more ideas, please let me know - I'm interested in learning all I can about Excel anyway (I used to think I was good until I came here - now I'm a noob again ;P).

 

[Aladin Akyurek]

It seems you don't want 0's included...

=SUMIF(A1:A50,"<>#DIV/0!")/MAX(1,COUNT(A1:A50)-COUNTIF(A1:A50,0))

Oops...yeah, I had a complete brain spasm on that one. The formula I'm using above excludes zeroes intentionally, but in this particular incident I *do* want to include zeroes. So the formula I have won't work either. Sorry - I'll try to make more sense and get some caffeine in me.

...

Then:

=SUMIF(A1:A50,"<>#DIV/0!")/COUNT(A1:A50)

should suffice.