Producing empty cell rather than #DIV/0!

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
 
A

agbiggs

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
 
G

Graham Whitehead

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.
 
A

agbiggs

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
 
B

Bob Phillips

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

--
HTH

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top