Minimum Function Problem

[3smees23]

Hi,

I want to select the lowest of three values in cells I21, I22 & I23. Another condition is that the formula should not select the lowest if it happens to be a zero.

eg,

If i have 0, 100 & 200 I need the formula to select the 100 value.

Cheers for you help

Sam

[sweep]

Hi,

Try this array:

=MIN(IF(I21:I23=0,"",I21:I23))

To confirm use CTRL, SHIFT and ENTER, if done correctly, excel will display:

{=MIN(IF(I21:I23=0,"",I21:I23))}

[DonkeyOte]

or non-array alternative

=SMALL(I21:I23,COUNTIF(I21:I123,"<=0")+1)

[3smees23]

Thanks Guys,

I went for the second option. The only problem is that if there is not a value in I21:I23 then it displays #NUM!. I basically cannot have this as its getting pulled into a program that does our job costing. How can I make this #NUM! value zero?

Regards,

Sam

[DonkeyOte]

A few options, one would be:

=IF(COUNTIF(I21:I23,">0"),SMALL(I21:I23,COUNTIF(I21:I23,"<=0")+1),0)

[3smees23]

Thanks DonkeyOte