MIN/MAX vs. IF() when cells are blank

[ajetrumpet]

all,

is there a justification for using MIN/MAX when I have a max ceiling number for a cell? MIN/MAX functions ignore blank cells, via excel help files. so, using them with a blank cell would require:

=min(if(isblank(c4), 0, c4), 4000)

whereas a simple IF() is only (which yields a 0 for blank cells):

if(c4>4000,c4,4000)

that makes MIN/MAX absolutely useless if blank cells are a risk.

an easier way to get around it? or is this set in stone? (I'm looking for ways to write the shortest possible formulas in cells so I don't get confused in the future when I'm looking at my own stuff.

thanks!

 

[repairman615]

Hello,

Have you considered:

<TABLE style="WIDTH: 132pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=176><COLGROUP><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 2816" width=176><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 132pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2653128 class=xl63 height=21 width=176>=MIN(0,C4,4000)</TD></TR></TBODY></TABLE>

 

[ajetrumpet]

Hello,

Have you considered:

<TABLE style="WIDTH: 132pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=176><COLGROUP><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 2816" width=176><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 132pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2653128 class=xl63 height=21 width=176>=MIN(0,C4,4000)</TD></TR></TBODY></TABLE>

okie doke. so numbers are considered less than blanks?? That's great to know!

thanks a lot!

 

[T. Valko]

okie doke. so numbers are considered less than blanks?? That's great to know!

thanks a lot!

Numbers have a lower value than TEXT.

=100000000000<"x" = TRUE
="x">100000000000 = TRUE

MIN and MAX will ignore empty cells. However, if every referenced cell is empty then both MIN and MAX will return 0.