Excel 2003 - using MIN correctly

  • Thread starter Thread starter Guest3731
  • Start date Start date
G

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 -
 
Bob Umlas said:
Shorter:

=IF(MIN(1*F26,1*F28)>0,MIN(F26,F28),0)
Click to expand...
....

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

=MAX(0,MIN(N(F26),N(F28)))
 
RagDyer said:
Would you consider this as 2 less function calls?

=MAX(0,MIN(--(A1),--(B1)))
Click to expand...
....

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))
 
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.
 
Back
Top