MIN IF

[Chris Ashley]

Does anyone know of a formula which will give the minimum value of a
range (ex. A1:A200) excluding the zeros (0).

 

[Frank Kabel]

Hi
try the array formula (entered with CTRL+SHIFT+ENTER)
=MIN(IF(A1:A200>0,A1:A200))

 

[Trevor Shuttleworth]

Chris

a previous reply to a similar question:

Hi,

Use an array formula:

=MIN(IF(A1:A100>0,A1:A100,""))

press control-shift-enter simultaneously when entering
this formula.

Regards,

Jan Karel Pieterse
Excel TA/MVP

Regards

Trevor

 

[Trevor Shuttleworth]

Another option:

Based on a solution from Gord Dibben, XL MVP -

=SMALL(A:A,COUNTIF(A:A,0)+1)

(NOT Array-entered but I don't think it hurts if it is)

Regards

Trevor

 

[David McRitchie]

The following will return the lowest number whether 0 or negative
and the poster definitely indicated no zeros.
=SMALL(A:A,COUNTIF(A:A,0)+1)

If there are no numbers in the column
the MIN formula returns #NUM!
the SMALL formula returns 0 -- zero

Reminder the MIN formula -- entered as array formula Ctrl+Shift+Enter
=MIN(IF(A1:A200>0,A1:A200))

IF you don't want to see the error you could code use Ctrl+Shift+Enter
=IF(COUNTIF(A1:A200,">0"),MIN(IF(A1:A200>0,A1:A200)),"")

 

[Chris Ashley]

Thanks guys. They both worked. What the heck does this do -- Ctrl+Shift+Enter

I never heard of that before, but it worked. Thanks a bunch.

 

[David McRitchie]

You specifically indicated NOT zero so there are two instances
then when SMALLS formula as presented fails and produces zero.
When you have zero as the lowest number and when you have no
numbers. Don't really know what you wanted for negative numbers.

Array Formulas are formulas that are working on elements of an
array individually like in a loop. You can read about Arrary Formulas
on Chip Pearson's site. Array Formulas Described
http://www.cpearson.com/excel/array.htm

 

[Trevor Shuttleworth]

David

If there are no numbers in the column
the MIN formula returns #NUM!
the SMALL formula returns 0 -- zero

I think this is actually the other way round:

the MIN formula returns 0
the SMALL formula returns #NUM!

But I hadn't considered negative numbers and SMALL does return 0 rather than
the negative number. OOPS

Regards

Trevor