How to read a blank cell as zero for formulas?

M

mrssm5

I have an empty cell because of "" in the formula but in a new formula
referencing that cell I want it to be read as zero? Help
 
D

Dave Peterson

Lots of excel's functions will treat that as a zero (like =sum()).

But some will blow up real good:
=a1+a2

Maybe:
=n(a1)+n(a2)

would be ok for you.
 
J

Jerry W. Lewis

Range based functions (SUM, AVERAGE, etc) will ignore "", but if you
want it treated as zero (instead of being ignored) you will have to do
something like
=IF(cell="",0,cell)
In a range based function this becomes an array function, like
=AVERAGE(IF(range="",0,range))

Note that cell="" does not distinguish between truly empty cells and
cells that contain "". If that distinction is important, Then you would
need to add a test for ISBLANK(cell).

Jerry
 
Top