Min Function

M

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
 
D

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))
 
M

Mike H

Try

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

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

Mike
 
J

John C

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

where myRange is your range of data.

Hope this helps.
 
P

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
 
M

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
 
M

matt3542

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

Cheers
Matt
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top