Minimum Function Problem

**3smees23** · 05-18-2009, 08:36 AM

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** · 05-18-2009, 08:40 AM

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** · 05-18-2009, 09:15 AM

or non-array alternative

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

**3smees23** · 05-18-2009, 09:37 AM

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