Min Function
 

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

Min Function
 

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"matt3542" wrote in message
...
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

Min Function
 

Try

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

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

Mike


"matt3542" wrote:

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

Min Function
 

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

where myRange is your range of data.

Hope this helps.
--
John C


"matt3542" wrote:

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

Min Function
 

Try this array* formula:

=MIN(IF(A1:A100,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

On Jul 30, 2:30*pm, matt3542
wrote:
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

Min Function
 

Many Thanks Mike, that worked perfectly, much appreciated
Matt

"Mike H" wrote:

Try

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

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

Mike


"matt3542" wrote:

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

Min Function
 

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

"Don Guillett" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"matt3542" wrote in message
...
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

Min Function
 

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

Cheers
Matt

"John C" wrote:

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

where myRange is your range of data.

Hope this helps.
--
John C


"matt3542" wrote:

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

Min Function
 

Many Thanks John, this also worked, much appreciated

Cheers
Matt

"Pete_UK" wrote:

Try this array* formula:

=MIN(IF(A1:A100,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

On Jul 30, 2:30 pm, matt3542
wrote:
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