Producing empty cell rather than #DIV/0!

  • Thread starter Thread starter agbiggs
  • Start date Start date
A

agbiggs

I'm averaging a number of cells, some of which contain the value
#DIV/0! because the cells' formula divides empty cells. The AVERAGE
function can't handle #DIV/0!. Is there any easy way to avoid this? I'm
thinking of an IF function such that if the formula's answer is #DIV/0!
it instead enters an empty cell. I'm sure this is possible, but having
a hard time getting things together. Any help appreciated!

Thanks,

Andrew
 
Thanks very much. To simplify, the formula in a given cell is equal to
something like =(A1-B1)/A1, where A1 equals a worker's earnings and B1
his spending, so the result equals his saving rate. THis works fine for
years in which the worker has earnings, but if A1 is empty then it
produces a #DIV/0! error.

I'd think that some sort of IF function would do the trick, but not
sure how to do it. I've looked at the ISERR function, but don't quite
understand it.

Thanks again,

Andrew
 
I think you are looking for this function:
=IF(ISERROR((A5-B5)/A5),0,(A5-B5)/A5)
basically, if it returns an error the result will be 0, otherwise it will
return the value of the equation.
 
Thanks - very helpful. Is it possible to have it return a blank cell
rather than a zero? I'm averaging the results of a number of cells and
so woudl want it to ignore rather than count that kind of cell.

Andrew
 
=IF(A5=0,"",(A5-B5)/A5)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top