Return the 2 lowest numbers within a range
 

Hey,
How can search a range of numbers (ex. E15:I15) and have it find the 2
lowest numbers in the range (most likely 0 and 0) and give me the header
number at the top of the column. And if there's more than 2, give me all of
the lowest possible numbers.

Ex.
Headers - 1 2 3 4 8
1 0 1 2 0

Then I would want the formula to give me the header of that row which the
two 0's are in (so it would give me "2,8".

Thanks!
Matt

Return the 2 lowest numbers within a range
 

=SMALL(A1:A10,{2})
=MIN(A1:A10)

Does that help?

--
RyGuy


"mpenkala" wrote:

Hey,
How can search a range of numbers (ex. E15:I15) and have it find the 2
lowest numbers in the range (most likely 0 and 0) and give me the header
number at the top of the column. And if there's more than 2, give me all of
the lowest possible numbers.

Ex.
Headers - 1 2 3 4 8
1 0 1 2 0

Then I would want the formula to give me the header of that row which the
two 0's are in (so it would give me "2,8".

Thanks!
Matt

Return the 2 lowest numbers within a range
 

I guess you want the result(s) in a single cell?

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then use this array formula**:

=SUBSTITUTE(TRIM(MCONCAT(IF(E16:I16=MIN(E16:I16),E 15:I15,"")&" "))," ",",")

Note that this is limited to a max returned string length of 255 characters
(including the delimiter).

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hey,
How can search a range of numbers (ex. E15:I15) and have it find the 2
lowest numbers in the range (most likely 0 and 0) and give me the header
number at the top of the column. And if there's more than 2, give me all
of
the lowest possible numbers.

Ex.
Headers - 1 2 3 4 8
1 0 1 2 0

Then I would want the formula to give me the header of that row which the
two 0's are in (so it would give me "2,8".

Thanks!
Matt

Return the 2 lowest numbers within a range
 

Hey T(Biff), this is working great, thanks. But is it possible to use the
formula you gave below and get the column header instead of the 0's? The
formula works great but returns all the lowest numbers (0,0), but I'm hoping
it will return the heard of that column instead. Possible?

Thanks again!
Matt


"T. Valko" wrote:

I guess you want the result(s) in a single cell?

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then use this array formula**:

=SUBSTITUTE(TRIM(MCONCAT(IF(E16:I16=MIN(E16:I16),E 15:I15,"")&" "))," ",",")

Note that this is limited to a max returned string length of 255 characters
(including the delimiter).

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hey,
How can search a range of numbers (ex. E15:I15) and have it find the 2
lowest numbers in the range (most likely 0 and 0) and give me the header
number at the top of the column. And if there's more than 2, give me all
of
the lowest possible numbers.

Ex.
Headers - 1 2 3 4 8
1 0 1 2 0

Then I would want the formula to give me the header of that row which the
two 0's are in (so it would give me "2,8".

Thanks!
Matt

Return the 2 lowest numbers within a range
 

It does return the column header. In the formula E15:I15 are the column
headers and E16:I16 are the numbers to match the min.

After reading your post again I think I may have it backwards. So, let's
assume E1:I1 are the column headers and E15:I15 are the numbers (array
entered):

=SUBSTITUTE(TRIM(MCONCAT(IF(E15:I15=MIN(E15:I15),E 1:I1,"")&" "))," ",",")


--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hey T(Biff), this is working great, thanks. But is it possible to use the
formula you gave below and get the column header instead of the 0's? The
formula works great but returns all the lowest numbers (0,0), but I'm
hoping
it will return the heard of that column instead. Possible?

Thanks again!
Matt


"T. Valko" wrote:

I guess you want the result(s) in a single cell?

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then use this array formula**:

=SUBSTITUTE(TRIM(MCONCAT(IF(E16:I16=MIN(E16:I16),E 15:I15,"")&" ")),"
",",")

Note that this is limited to a max returned string length of 255
characters
(including the delimiter).

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hey,
How can search a range of numbers (ex. E15:I15) and have it find the 2
lowest numbers in the range (most likely 0 and 0) and give me the
header
number at the top of the column. And if there's more than 2, give me
all
of
the lowest possible numbers.

Ex.
Headers - 1 2 3 4 8
1 0 1 2 0

Then I would want the formula to give me the header of that row which
the
two 0's are in (so it would give me "2,8".

Thanks!
Matt

Return the 2 lowest numbers within a range
 

Excellent work once again! Works like a charm.
Appriciate the help (as always!)

Matt


"T. Valko" wrote:

It does return the column header. In the formula E15:I15 are the column
headers and E16:I16 are the numbers to match the min.

After reading your post again I think I may have it backwards. So, let's
assume E1:I1 are the column headers and E15:I15 are the numbers (array
entered):

=SUBSTITUTE(TRIM(MCONCAT(IF(E15:I15=MIN(E15:I15),E 1:I1,"")&" "))," ",",")


--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hey T(Biff), this is working great, thanks. But is it possible to use the
formula you gave below and get the column header instead of the 0's? The
formula works great but returns all the lowest numbers (0,0), but I'm
hoping
it will return the heard of that column instead. Possible?

Thanks again!
Matt


"T. Valko" wrote:

I guess you want the result(s) in a single cell?

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then use this array formula**:

=SUBSTITUTE(TRIM(MCONCAT(IF(E16:I16=MIN(E16:I16),E 15:I15,"")&" ")),"
",",")

Note that this is limited to a max returned string length of 255
characters
(including the delimiter).

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hey,
How can search a range of numbers (ex. E15:I15) and have it find the 2
lowest numbers in the range (most likely 0 and 0) and give me the
header
number at the top of the column. And if there's more than 2, give me
all
of
the lowest possible numbers.

Ex.
Headers - 1 2 3 4 8
1 0 1 2 0

Then I would want the formula to give me the header of that row which
the
two 0's are in (so it would give me "2,8".

Thanks!
Matt

Return the 2 lowest numbers within a range
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Excellent work once again! Works like a charm.
Appriciate the help (as always!)

Matt


"T. Valko" wrote:

It does return the column header. In the formula E15:I15 are the column
headers and E16:I16 are the numbers to match the min.

After reading your post again I think I may have it backwards. So, let's
assume E1:I1 are the column headers and E15:I15 are the numbers (array
entered):

=SUBSTITUTE(TRIM(MCONCAT(IF(E15:I15=MIN(E15:I15),E 1:I1,"")&" "))," ",",")


--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hey T(Biff), this is working great, thanks. But is it possible to use
the
formula you gave below and get the column header instead of the 0's?
The
formula works great but returns all the lowest numbers (0,0), but I'm
hoping
it will return the heard of that column instead. Possible?

Thanks again!
Matt


"T. Valko" wrote:

I guess you want the result(s) in a single cell?

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then use this array formula**:

=SUBSTITUTE(TRIM(MCONCAT(IF(E16:I16=MIN(E16:I16),E 15:I15,"")&" ")),"
",",")

Note that this is limited to a max returned string length of 255
characters
(including the delimiter).

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
Hey,
How can search a range of numbers (ex. E15:I15) and have it find the
2
lowest numbers in the range (most likely 0 and 0) and give me the
header
number at the top of the column. And if there's more than 2, give me
all
of
the lowest possible numbers.

Ex.
Headers - 1 2 3 4 8
1 0 1 2 0

Then I would want the formula to give me the header of that row
which
the
two 0's are in (so it would give me "2,8".

Thanks!
Matt