Min <> 0

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've seen other posts for this same type of question, find the smallest
number not equal to zero, but the following still returns a zero. Any
suggestions?

=MIN(IF(Q33<>0,Q33),IF(BC33<>0,BC33),IF(BM33<>0,BM33),IF(CA33<>0,CA33),IF(CO33<>0,CO33),IF(DC33<>0,DC33),IF(DP33<>0,DP33),IF(EB33<>0,EB33),IF(EN33<>0,EN33))
 
St@cy said:
I've seen other posts for this same type of question, find the smallest
number not equal to zero, but the following still returns a zero. Any
suggestions?

=MIN(IF(Q33<>0,Q33),IF(BC33<>0,BC33),IF(BM33<>0,BM33),
IF(CA33<>0,CA33),IF(CO33<>0,CO33),IF(DC33<>0,DC33),
IF(DP33<>0,DP33),IF(EB33<>0,EB33),IF(EN33<>0,EN33))
Click to expand...

I'll guess you're really after the smallest positive number, in which
case, try

=SMALL((Q33,BC33,BM33,CA33,CO33,DC33,DP33,EB33,EN33),
INDEX(FREQUENCY((Q33,BC33,BM33,CA33,CO33,DC33,DP33,EB33,EN33),
{0}),1)+1)
 
I think what is happening is your IF statements are returning FALSE for the
for the cells that are 0. Since FALSE is a valid argument when your
arguments are not passed via a range or array reference, MIN returns 0 (which
is the underlying value of FALSE). See XL help for MIN.

The examples you've seen to find the MIN of a range (using IF to weed out
the 0's) were likely used in an array formula - a situation in which MIN will
ignore FALSE arguments.

Perhaps you could try naming the range (select cell Q33, then hold down the
control key and select the rest of the cells, then enter a name in the name
box), and try

where the named range is Test:
=IF(MIN(Test),MIN(Test),SMALL(Test,INDEX(FREQUENCY(Test,0),1)+1))

Or, without using a named range, try:
=IF(MIN((Q33, BC33, BM33, CA33,CO33,DC33,DP33, EB33, EN33)),MIN((Q33, BC33,
BM33, CA33,CO33,DC33,DP33, EB33, EN33)),SMALL((Q33, BC33, BM33,
CA33,CO33,DC33,DP33, EB33, EN33),INDEX(FREQUENCY((Q33, BC33, BM33,
CA33,CO33,DC33,DP33, EB33, EN33),0),1)+1))
 
Back
Top