PDA

View Full Version : Solved: Ignoring 0 in list

SparrowHawk
02-24-2007, 11:01 AM
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

lucas
02-24-2007, 11:04 AM
http://www.mrexcel.com/archive/Dates/11449.html

I just searched google with this string:

cell 0 Min formula function excel

Bob Phillips
02-24-2007, 11:10 AM
=MIN(IF(A1:A10<>0,A1:A10))

as an array formula, committed with Ctrl-Shift-Enter, not just Enter.

lucas
02-24-2007, 11:21 AM
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))

mdmackillop
02-24-2007, 11:29 AM
Just fixed that :thumb

Bob Phillips
02-24-2007, 11:51 AM
Copy and paste bug :bug:

SparrowHawk
02-24-2007, 12:09 PM
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

lucas
02-24-2007, 12:21 PM
Good deal Ken. Be sure to mark your thread solved using the thread tools at the top of the page...

Shazam
02-24-2007, 05:10 PM
Here is an non-array approach.

=LARGE(A1:A10,COUNTIF(A1:A10,">0"))

Hope it helps!