How to read a blank cell as zero for formulas?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
You must log in or register to reply here.

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

Excel formula, sum the value cell and if empty cell left blank 0
need help with formula 7
Blank cells in a formula 2
Blank Cell 5
Empty Date Cell as Zero 1
Difference of empty cell and zero value when formatting 4
Leading Zero 11
cells with zero value are shown as blank, not zero 2

Back
Top