Forum Discussion
I have a question about the MIN formula
I'm trying to find the minimum value in a column of numbers, but if the value is 0 I don't want to consider it. In other words, I'd like to ignore the zeros. I can't seem to find a way to do this with the MIN function. Does anyone know how to do this? Is there a different function I should be using?
6 Replies
- Thank you!!
The Minifs function suggested by Detlef_Lewin is an excellent choice, but it is only available if you have an Office 365 subscription.
If you have a different Office license, then you can try this formula:
=MIN(IF(A1:A5>0,A1:A5))
It is an array formula and must be confirmed with Ctrl-Shift-Enter, but it works in all versions of Excel.
Just for the collection, non-array formula
=AGGREGATE(15,6,(1/A1:A5>0)*A1:A5,1)
if Excel 2010 and later
Still for the collection a non-array solution that works with all versions of Excel:
=SUMPRODUCT(MIN( ((A1:A5=0)*(MAX(A1:A5)+1)) +A1:A5))
- Thanks. I have Office 365.
Lynne,
use MINFIS().
=MINIFS(A1:A6,A1:A6,">0")
