Why are blanks being ignored by my MIN function?
 

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)

Why are blanks being ignored by my MIN function?
 

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)

Why are blanks being ignored by my MIN function?
 

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)

Why are blanks being ignored by my MIN function?
 

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)

Why are blanks being ignored by my MIN function?
 

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)

Why are blanks being ignored by my MIN function?
 

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)

Why are blanks being ignored by my MIN function?
 

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)

Why are blanks being ignored by my MIN function?
 

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