| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup range of numbers; return corresponding data for the fixed r | Excel Worksheet Functions | |||
| I need to return the lowest time value in a range of cells. | Excel Worksheet Functions | |||
| How to return a single value for a range of numbers | Excel Worksheet Functions | |||
| Range vs. lowest #, 2nd lowest #, 3rd lowest #, etc | Excel Discussion (Misc queries) | |||
| How can I get the lowest price, second lowest etc. from a range o. | Excel Worksheet Functions | |||
Hybrid Mode
