Excel 2003 - using MIN correctly

[Guest3731]

This is a bit like my last question. I want to fill a cell with
either the minimum of two values, or, if that minimum is either zero
or negative, then 0. I'm using

=IF(MIN(F26,F28)>0,MIN(F26,F28),0)

However, if cell f26 is empty, this formula will show the value of
f28, rather than showing 0 as I had intended. Is there a version of
this formula I can use that will automatically assume that blank cells
= 0?

Thanks -

 

[Bernie Deitrick]

=IF(OR(F26="",F28=""),0,MAX(MIN(F26,F28),0))

HTH,
Bernie
MS Excel MVP

 

[Guest3731]

=IF(OR(F26="",F28=""),0,MAX(MIN(F26,F28),0))

Beautiful, thanks.

 

[Bob Umlas]

Shorter:

=IF(MIN(1*F26,1*F28)>0,MIN(F26,F28),0)
Bob Umlas
Excel MVP

 

[Harlan Grove]

Shorter:

=IF(MIN(1*F26,1*F28)>0,MIN(F26,F28),0)

....

Oh, you want SHORT. Then why not make it as short as possible?

=MAX(0,MIN(N(F26),N(F28)))

 

[RagDyer]

Would you consider this as 2 less function calls?

=MAX(0,MIN(--(A1),--(B1)))

 

[Harlan Grove]

Would you consider this as 2 less function calls?

=MAX(0,MIN(--(A1),--(B1)))

....

Yes, except that the formula would return #VALUE! errors if A1 or B1
contained strings of spaces which would appear blank. Using the N
calls treats ANY text as numeric zeros.

If you could assume that cells A1 and B1 would only ever contain
numbers or be truly blank, you could use

=-MIN(0,MAX(-A1,-B1))

 

[Rick Rothstein]

I really have no excuse for this except that it is a slow weekend (and I
figured you might get a kick out of it)<g>...

Not shorter, but *way* more obfuscated...

=(F26+F28-ABS(F26-F28))/4+ABS((F26+F28-ABS(F26-F28))/4)

Although if we define the name X for the formula....

=($F$26+$F$28-ABS($F$26-$F$28))/4

then we can use this simplified formula on the worksheet...

=X+ABS(X)

choosing any convenient name in place of X that you might want.