Empty Cells as Zeros

  • Thread starter Thread starter koala824 at Comcast
  • Start date Start date
K

koala824 at Comcast

How can I get formulas to treat empty cells as if they held the value of
'zero' ? Thanks. Ted
 
If they're really empty, then excel should treat them as 0's in arithmetic
operations.

If they have space characters (or other whitespace characters), then this isn't
the case.

And if they once contained formulas that evaluated to "" (an empty string), then
converted to values, you'll have trouble with those until you clear the contents
of those cells.

So...

What formula/function doesn't treat the empty cells as 0?

And are you sure those cells are empty?

You can use =counta(a1) to test to see if a1 is really empty. If you see 0,
then it's empty. If you see 1, then it's not empty.
 
That is already Excel default for blank cells.

=SUM(a1:a10) returns 0 if a1:a10 are blank.

By "empty" do you mean those cells that return "" by formula like

=IF(a1*b1<10,0,a1*b1)

Those cells are not "empty"

Explain what "empty" means to you.


Gord Dibben MS Excel MVP
 
I am evaluating the following formula:

=MAX(0,MIN(M46,I46)) with

M46 = blank, applied 'clear contents'
I46 = 58.90

I expect a result of 0 if M46 is evaluated as 0, as desired. However, the
result is 58.90. If I explicitly enter 0 in M46, I get the correct answer.
Thanks for taking an interest.
 
You could build an an =if() statement:

=max(0,min(if(isnumber(m46),m46,0),if(isnumber(i46),i46,0)))

Or you could use excel's =n() function:

=max(0,min(n(M46),n(I46)))
 
Back
Top