Producing empty cell rather than #DIV/0!

[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

 

[Don Guillett]

post your formula and/or have a look in help index for ISERR

 

[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

 

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

 

[Don Guillett]

did you look in the help index as suggested?

 

[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

 

[agbiggs]

Yes (and in my Excel reference book). Not very clear, unfortunately.

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[daddylonglegs]

Try

=IF(A1,1-B1/A1,"")

 

[agbiggs]

Thanks!