I want to have a cell tell me the minimum value in a list of cells except to ignore zeros. I can't seem to figure out the syntax with anything. MIN(F3:F46) includes the zeros so, of course, the answer is 0. How can I get it to ignore zero?
TIA,
Ken
I want to have a cell tell me the minimum value in a list of cells except to ignore zeros. I can't seem to figure out the syntax with anything. MIN(F3:F46) includes the zeros so, of course, the answer is 0. How can I get it to ignore zero?
TIA,
Ken
http://www.mrexcel.com/archive/Dates/11449.html
I just searched google with this string:
cell 0 Min formula function excel
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
=MIN(IF(A1:A10<>0,A1:A10))
as an array formula, committed with Ctrl-Shift-Enter, not just Enter.
I have learned some things from Bob especially with regard to formula's so I would try his contribution.....
But when I tried it I found it was missing a trailing parenthesis:
=MIN(IF(A1:A10<>0,A1:A10))
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Just fixed that
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Copy and paste bug
Thank all of you for such a quick reply. It was the syntax I had trouble understanding but after looking at the examples you gave I understand now.
Thanks again,
Ken
Good deal Ken. Be sure to mark your thread solved using the thread tools at the top of the page...
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Here is an non-array approach.
=LARGE(A1:A10,COUNTIF(A1:A10,">0"))
Hope it helps!
SHAZAM!
Posting Permissions
Reply With Quote
