Empty Cells as Zeros

[koala824 at Comcast]

How can I get formulas to treat empty cells as if they held the value of
'zero' ? Thanks. Ted

 

[Dave Peterson]

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.

 

[Gord Dibben]

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

 

[koala824 at Comcast]

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.

 

[Dave Peterson]

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)))

 

[Boopathi]

Hi Try this

=MAX(0,MIN(IF(ISBLANK(M46),0,M46),I46))