MIN IF

C

Chris Ashley

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

Frank Kabel

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

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
 
T

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
 
D

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)),"")
 
C

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 said:
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)),"")

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Trevor Shuttleworth said:
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
Click to expand...
Click to expand...
 
D

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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Chris Ashley said:
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 said:
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)),"")

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Trevor Shuttleworth said:
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


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

Trevor Shuttleworth

David
If there are no numbers in the column
the MIN formula returns #NUM!
the SMALL formula returns 0 -- zero
Click to expand...

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


David McRitchie said:
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)),"")

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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
Click to expand...
Click to expand...
 
Top