DIV/0!

[Harvey Waxman]

Is there a way to add a column that contains some #DIV/0! values and ignore
those cells in the calculation? Or do I have to provide for the DIV in the
individual cells with an IF statement of some kind?

Thanks

 

[Helpful Harry]

Is there a way to add a column that contains some #DIV/0! values and ignore
those cells in the calculation? Or do I have to provide for the DIV in the
individual cells with an IF statement of some kind?

You shouldn't be dividing by 0 since that's impossible. The "#DIV/0!"
is an error message saying the calculation can't be done, and since
that cell is referenced in the summing, that can't be done either.

You'll need to either fix the data in the cells referenced by the
division calculation, or if they are correct and can be 0 at times,
then you'll need to change the formula in the division column from:

= A1 / B1

to something like:

= If (B1 = 0, 0, A1 / B1) if you want 0's displayed

or = If (B1 = 0, "", A1 / B1) if you want the cells left empty

Both check to see if B1 is 0 (or empty) and if it is puts a 0 or ""
(nothing) into the cell, otherwise it does the division. Since there's
no longer any calculation errors the column to be summed and / or those
cells to be used in other calculations.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

 

[Ken Johnson]

Hi Harvey,
One way is use SUMIF with the condition "<9.99999999999999E307"
eg SUMIF(A:A,"<9.99999999999999E307")
9.99999999999999E307, (Fourteen 9's after the decimal point) is the
highest value that can be represeted in Excel, and it is unlikely that
your column to be summed will actually contain this value.
Ken Johnson

 

[Dr. Harvey Waxman]

Hi Harvey,
One way is use SUMIF with the condition "<9.99999999999999E307"
eg SUMIF(A:A,"<9.99999999999999E307")
9.99999999999999E307, (Fourteen 9's after the decimal point) is the
highest value that can be represeted in Excel, and it is unlikely that
your column to be summed will actually contain this value.
Ken Johnson

That is a neat trick. My particular problem is that the list needs to be
averaged not added. I guess I will just have to be more careful in the formula
that produces the data in the list to avoid the DIV/0 problem.

Might there be a way to do a find / replace on the column to remove those div's
all at once?

Many thanks

Harvey

 

[Ken Johnson]

Hi Harvey,
I've never made a User Defined Function before and this seemed like an
excellent opportunity for me to give it a go. I'm really pleased with
how it worked out and it wasn't so hard after all.
I'm suggesting you use this instead of the AVERAGE worksheet function
so that you won't have to worry about those #DIV/0!'s.
Paste this function into a VBA module (Alt + F11 gets you there the
fastest, and if there isn't a module visible then Insert>Module)

Public Function MeanSansErrors(Data As Variant) As Double
Application.Volatile
Const BigNumber As Double = 9.99999999999999E+307
MeanSansErrors = Application.SumIf(Data, "<" & BigNumber) _
/ Application.CountIf(Data, "<" & BigNumber)

End Function

With this UDF in place you can use it just like any other worksheet
function. It is available through the Insert menu by
Insert>Function..., but as far as I can tell it does not appear in the
Recently Used List, so you have to go to All to select it from the
dialog listbox.You can also get it just by typing "=MeanSansErrors" in
a cell and a dialog pops up for you to input the range of cells
containing the Data values. You could even edit its name in the VBA
module (heading and in the code) to something simpler eg MSE, provided
it doesn't conflict with any other function name.

Hope you find this useful Harvey, I've certainly gained a lot from this
little exercise.
BTW the little trick with the big number is something I read only a few
days ago from Bob Phillips' website Xldynamic.

Ken Johnson

 

[Dr. Harvey Waxman]

Hi Harvey,
I've never made a User Defined Function before and this seemed like an
excellent opportunity for me to give it a go. I'm really pleased with
how it worked out and it wasn't so hard after all. snip
Hope you find this useful Harvey, I've certainly gained a lot from this
little exercise.
BTW the little trick with the big number is something I read only a few
days ago from Bob Phillips' website Xldynamic.

Ken Johnson

Thanks Ken. I'll look at it.

Happy New Year to all