Min Function

[matt3542]

Dear Forum, I would be grateful if anyone could help me find a solution to
the following problem;

I have a column of data that follows the format below. I would like to use
the min function (or a suggested alternative) that would give me the minimum
value excluding any zero values, i.e if a zero value occurs I want the result
to ignore this and give me the next minimum value, i.e in the context of the
data below 1 would be the correct result returned;

0
1
0
3
5
1
7

Many Thanks
Matt

 

[Don Guillett]

This is an array formula that must be entered using ctrl+shift+enter

=MIN(IF(A2:A22>0,A2:A22))
if you also want to look at negative numbers
=MIN(IF(A2:A22<>0,A2:A22))

 

[Mike H]

Try

=MIN(IF(A1:A100 >0,A1:A100))

Which is an array so commit with CTRL+Shift+Enter NOT just enter

Mike

 

[John C]

Tested on your data set, and works:
=LARGE(myRange,COUNTIF(myRange,">0"))

where myRange is your range of data.

Hope this helps.

 

[Pete_UK]

Try this array* formula:

=MIN(IF(A1:A10>0,A1:A10,10E10))

Adjust the ranges to suit.

* An array formula must be committed using Ctrl-Shift-Enter (CSE)
instead of the usual <Enter>. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - do not type these yourself. If you need to amend the formula,
use CSE again.

10E10 is just a big number, so this does not contribute to the
minimum.

Hope this helps.

Pete

 

[matt3542]

Many Thanks Mike, that worked perfectly, much appreciated
Matt

 

[matt3542]

Thanks also Don, that worked perfectly just like Mike's. Also cheers for
giving me a solution for a negative value scenario, it will come prove useful
for future work

Cheers Matt

 

[matt3542]

Many thanks John for providing this alternative example, much appreciated. I
think its always nice to have options available.

Cheers
Matt

 

[matt3542]

Many Thanks John, this also worked, much appreciated

Cheers
Matt