in Excel 2003 I have a formula =MIN (100, A1)

If A1 is blank it returns 100 but if A1=0 then it returns 0.

Why doesn't it see the blank as a 0? Is there a setting or option that I am
missing? I didn't have this problem in Excel 2000 (my work recently upgraded
us)

The MIN function ignores blanks...that's usually preferred.

Try this:
=MIN(100,N(A1))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"catherine bodine" <catherine wrote in
message ...
in Excel 2003 I have a formula =MIN (100, A1)

If A1 is blank it returns 100 but if A1=0 then it returns 0.

Why doesn't it see the blank as a 0? Is there a setting or option that I
am
missing? I didn't have this problem in Excel 2000 (my work recently
upgraded
us)

The MIN function only evaluates numbers. Since a blank is not a number, the
MIN function ignores it. Try this:

=MIN(100,IF(A1="",0,A1))

HTH,
Elkar


"catherine bodine" wrote:

in Excel 2003 I have a formula =MIN (100, A1)

If A1 is blank it returns 100 but if A1=0 then it returns 0.

Why doesn't it see the blank as a 0? Is there a setting or option that I am
missing? I didn't have this problem in Excel 2000 (my work recently upgraded
us)

Thanks Ron

I don't know how I never ran into this problem before. I didn't know about
N() and that is a very elegant solution. I didn't want to have to resort to
an if/then to fix.

-Catherine

"Ron Coderre" wrote:

The MIN function ignores blanks...that's usually preferred.

Try this:
=MIN(100,N(A1))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"catherine bodine" <catherine wrote in
message ...
in Excel 2003 I have a formula =MIN (100, A1)

If A1 is blank it returns 100 but if A1=0 then it returns 0.

Why doesn't it see the blank as a 0? Is there a setting or option that I
am
missing? I didn't have this problem in Excel 2000 (my work recently
upgraded
us)

Just to point out, the use of N(A1) will treat any text string as 0 as well,
instead of just blanks. This may not be an issue, but just something to keep
in mind.

HTH,
Elkar


"Ron Coderre" wrote:

The MIN function ignores blanks...that's usually preferred.

Try this:
=MIN(100,N(A1))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"catherine bodine" <catherine wrote in
message ...
in Excel 2003 I have a formula =MIN (100, A1)

If A1 is blank it returns 100 but if A1=0 then it returns 0.

Why doesn't it see the blank as a 0? Is there a setting or option that I
am
missing? I didn't have this problem in Excel 2000 (my work recently
upgraded
us)

I'm glad I could help.....
Thanks for the feedback....and the kind words. :)

***********
Regards,
Ron

XL2003, WinXP


"catherine bodine" wrote:

Thanks Ron

I don't know how I never ran into this problem before. I didn't know about
N() and that is a very elegant solution. I didn't want to have to resort to
an if/then to fix.

-Catherine

"Ron Coderre" wrote:

The MIN function ignores blanks...that's usually preferred.

Try this:
=MIN(100,N(A1))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"catherine bodine" <catherine wrote in
message ...
in Excel 2003 I have a formula =MIN (100, A1)

If A1 is blank it returns 100 but if A1=0 then it returns 0.

Why doesn't it see the blank as a 0? Is there a setting or option that I
am
missing? I didn't have this problem in Excel 2000 (my work recently
upgraded
us)

Just out of curiosity, why would you want to return zero for a blank cell?


--


Regards,


Peo Sjoblom


"catherine bodine" wrote in
message ...
Thanks Ron

I don't know how I never ran into this problem before. I didn't know
about
N() and that is a very elegant solution. I didn't want to have to resort
to
an if/then to fix.

-Catherine

"Ron Coderre" wrote:

The MIN function ignores blanks...that's usually preferred.

Try this:
=MIN(100,N(A1))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"catherine bodine" <catherine wrote in
message ...
in Excel 2003 I have a formula =MIN (100, A1)

If A1 is blank it returns 100 but if A1=0 then it returns 0.

Why doesn't it see the blank as a 0? Is there a setting or option that
I
am
missing? I didn't have this problem in Excel 2000 (my work recently
upgraded
us)

One mo

=MIN(100,A1+0)

But only if A1 is always empty or a number.

catherine bodine wrote:

in Excel 2003 I have a formula =MIN (100, A1)

If A1 is blank it returns 100 but if A1=0 then it returns 0.

Why doesn't it see the blank as a 0? Is there a setting or option that I am
missing? I didn't have this problem in Excel 2000 (my work recently upgraded
us)

--

Dave Peterson