Log In
Register
Home
March 10, 2016
Offline
hi
I have a column with data that says zero
I want to get the minimum value from the column and it pulls me zero
How can I get the minimum value and not the zero?
Thank you!
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Lea
Assuming your data is in the range A1:A10 and their values are 0 and above (no negative values).
=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)
I am using Excel 2010.
Hope this helps.
Sunny
Answers Post
July 5, 2019
Offline
That's a good one Sunny. I would not have thought of that, but since you offered that up, it got me thinking about negative numbers, so this formula should also handle it if there are negative numbers:
=SMALL(A1:A10,COUNTIF(A1:A10,"<="&0)+1)
March 10, 2016
Offline
thanks for the reply!
How do I run the formula in the attachment?
Where I have tested the formula in the cells are not linked.
To get the lowest price from all the total columns - are marked yellow
Ignore when total zero.
Thank you very much for the response !!
Leah
July 5, 2019
Offline
I don't know what you are asking. What column (please supply a column header, like "Column Q") are you looking to get the minimum number, excluding 0?
VIP
Trusted Members
June 25, 2016
Offline
Hi Lea
Try this. It is an array formula so press CTRL+SHIFT+ENTER instead of ENTER to complete the formula.
In cell T3 enter =MIN(IF($J$2:$R$2="sum",IF(J3:R3 <> 0,J3:R3)))
Hope this helps.
Sunny
1 Guest(s)
