| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index/Match maybe???
Excel2003 ...
Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
Try this:
Enter this formula in Q3 as an array**: =IF(COLUMNS($A:A)<=COUNTIF($B3:$P3,MAX($B3:$P3)),I NDEX($B$2:$P$2,SMALL(IF($B3:$P3=MAX($B3:$P3),COLUM N($B3:$P3)-MIN(COLUMN($B3:$P3))+1),COLUMNS($A:A))),"") Copy across to a number of cells that will equal the maximum number of ties in any of the rows. For example, if there are a max of 3 ties in any single row then you need to copy the formula across to at least 3 columns. Then copy down as needed. ** entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
One way:
=INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a
moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask T. Valko same thing ... His formula is flawless too. Thank you for the guidance ... Kha "Trevor Shuttleworth" wrote: One way: =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
T. ... Formula works flawlessly (Thanks) ... That said ... Can you take a
moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask Trevor same thing ... His formula works fine too for what it does. Thank you for the guidance ... Kha "T. Valko" wrote: Try this: Enter this formula in Q3 as an array**: =IF(COLUMNS($A:A)<=COUNTIF($B3:$P3,MAX($B3:$P3)),I NDEX($B$2:$P$2,SMALL(IF($B3:$P3=MAX($B3:$P3),COLUM N($B3:$P3)-MIN(COLUMN($B3:$P3))+1),COLUMNS($A:A))),"") Copy across to a number of cells that will equal the maximum number of ties in any of the rows. For example, if there are a max of 3 ties in any single row then you need to copy the formula across to at least 3 columns. Then copy down as needed. ** entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
OK
MAX(B3:P3) returns the maximum value in the range B3 to P3 MATCH(MAX(B3:P3),B3:P3,0) returns the offset of the maximum value within the range MATCH(MAX(B3:P3),B3:P3,0)+1 calculates the column number (taking into account we start in column B) ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) turns that into an address ... row 2, column whatever we just calculated =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) gets the value from the calculated address So, let's say the maximum value of 9 was in cell L3 which is column 12 =MAX(B3:P3) = 9 =MATCH(MAX(B3:P3),B3:P3,0) = MATCH(9,B3:P3,0) = 11 =MATCH(MAX(B3:P3),B3:P3,0)+1 = MATCH(9,B3:P3,0)+1 = 12 =ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) = ADDRESS(2, 12) = $L$2 =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) = INDIRECT($L$2) = column 12 or whatever Regards Trevor "Ken" wrote in message ... Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask T. Valko same thing ... His formula is flawless too. Thank you for the guidance ... Kha "Trevor Shuttleworth" wrote: One way: =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
Trevor ... (Happy morning)
I placed values in the applicable Range of a Blank WorkBook & worked this formula thru to help me better understand the Formulas & sequence of calculations (excellent explanation) ... I have learned much from those that are intimate with Excel & support these boards ... Please know I appreciate the time you have taken to provide this guidance for one of my many Excel short-comings. My Thanks ... Have a Happy & Safe Day ... Kha "Trevor Shuttleworth" wrote: OK MAX(B3:P3) returns the maximum value in the range B3 to P3 MATCH(MAX(B3:P3),B3:P3,0) returns the offset of the maximum value within the range MATCH(MAX(B3:P3),B3:P3,0)+1 calculates the column number (taking into account we start in column B) ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) turns that into an address ... row 2, column whatever we just calculated =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) gets the value from the calculated address So, let's say the maximum value of 9 was in cell L3 which is column 12 =MAX(B3:P3) = 9 =MATCH(MAX(B3:P3),B3:P3,0) = MATCH(9,B3:P3,0) = 11 =MATCH(MAX(B3:P3),B3:P3,0)+1 = MATCH(9,B3:P3,0)+1 = 12 =ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) = ADDRESS(2, 12) = $L$2 =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) = INDIRECT($L$2) = column 12 or whatever Regards Trevor "Ken" wrote in message ... Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask T. Valko same thing ... His formula is flawless too. Thank you for the guidance ... Kha "Trevor Shuttleworth" wrote: One way: =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
Thanks for the feedback. You have a great day too
"Ken" wrote in message ... Trevor ... (Happy morning) I placed values in the applicable Range of a Blank WorkBook & worked this formula thru to help me better understand the Formulas & sequence of calculations (excellent explanation) ... I have learned much from those that are intimate with Excel & support these boards ... Please know I appreciate the time you have taken to provide this guidance for one of my many Excel short-comings. My Thanks ... Have a Happy & Safe Day ... Kha "Trevor Shuttleworth" wrote: OK MAX(B3:P3) returns the maximum value in the range B3 to P3 MATCH(MAX(B3:P3),B3:P3,0) returns the offset of the maximum value within the range MATCH(MAX(B3:P3),B3:P3,0)+1 calculates the column number (taking into account we start in column B) ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) turns that into an address ... row 2, column whatever we just calculated =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) gets the value from the calculated address So, let's say the maximum value of 9 was in cell L3 which is column 12 =MAX(B3:P3) = 9 =MATCH(MAX(B3:P3),B3:P3,0) = MATCH(9,B3:P3,0) = 11 =MATCH(MAX(B3:P3),B3:P3,0)+1 = MATCH(9,B3:P3,0)+1 = 12 =ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) = ADDRESS(2, 12) = $L$2 =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) = INDIRECT($L$2) = column 12 or whatever Regards Trevor "Ken" wrote in message ... Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask T. Valko same thing ... His formula is flawless too. Thank you for the guidance ... Kha "Trevor Shuttleworth" wrote: One way: =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
Trevor ... (Happy morning)
It has been a long week so I am just getting back to all of this ... Since I have had no further response from T. Valko & if you are still listening ... Would you be able to explain formula Valko provided in same way you explained yours (line item step by step) ... Not only is this helpful for me to better understand the specific functions, but also the combination of functions & order of calculations etc ... Formula is: =IF(COLUMNS($A:A)<=COUNTIF($B3:$P3,MAX($B3:$P3)),I NDEX($B$2:$P$2,SMALL(IF($B3:$P3=MAX($B3:$P3),COLUM N($B3:$P3)-MIN(COLUMN($B3:$P3))+1),COLUMNS($A:A))),"") Thank you in advance ... Kha "Trevor Shuttleworth" wrote: Thanks for the feedback. You have a great day too "Ken" wrote in message ... Trevor ... (Happy morning) I placed values in the applicable Range of a Blank WorkBook & worked this formula thru to help me better understand the Formulas & sequence of calculations (excellent explanation) ... I have learned much from those that are intimate with Excel & support these boards ... Please know I appreciate the time you have taken to provide this guidance for one of my many Excel short-comings. My Thanks ... Have a Happy & Safe Day ... Kha "Trevor Shuttleworth" wrote: OK MAX(B3:P3) returns the maximum value in the range B3 to P3 MATCH(MAX(B3:P3),B3:P3,0) returns the offset of the maximum value within the range MATCH(MAX(B3:P3),B3:P3,0)+1 calculates the column number (taking into account we start in column B) ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) turns that into an address ... row 2, column whatever we just calculated =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) gets the value from the calculated address So, let's say the maximum value of 9 was in cell L3 which is column 12 =MAX(B3:P3) = 9 =MATCH(MAX(B3:P3),B3:P3,0) = MATCH(9,B3:P3,0) = 11 =MATCH(MAX(B3:P3),B3:P3,0)+1 = MATCH(9,B3:P3,0)+1 = 12 =ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) = ADDRESS(2, 12) = $L$2 =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) = INDIRECT($L$2) = column 12 or whatever Regards Trevor "Ken" wrote in message ... Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask T. Valko same thing ... His formula is flawless too. Thank you for the guidance ... Kha "Trevor Shuttleworth" wrote: One way: =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
Ken
sorry, that would be a "no". Good though isn't it !? Best way is to Array Enter the formula into cell Q3 as suggested, then drag it across and look at how the formula changes in each column. You could also take any of the "inner" formulae and put them in a separate cell to evaluate them. Best I can suggest, I'm afraid. Perhaps Biff can be persuaded to clarify ? Regards Trevor "Ken" wrote in message ... Trevor ... (Happy morning) It has been a long week so I am just getting back to all of this ... Since I have had no further response from T. Valko & if you are still listening ... Would you be able to explain formula Valko provided in same way you explained yours (line item step by step) ... Not only is this helpful for me to better understand the specific functions, but also the combination of functions & order of calculations etc ... Formula is: =IF(COLUMNS($A:A)<=COUNTIF($B3:$P3,MAX($B3:$P3)),I NDEX($B$2:$P$2,SMALL(IF($B3:$P3=MAX($B3:$P3),COLUM N($B3:$P3)-MIN(COLUMN($B3:$P3))+1),COLUMNS($A:A))),"") Thank you in advance ... Kha "Trevor Shuttleworth" wrote: Thanks for the feedback. You have a great day too "Ken" wrote in message ... Trevor ... (Happy morning) I placed values in the applicable Range of a Blank WorkBook & worked this formula thru to help me better understand the Formulas & sequence of calculations (excellent explanation) ... I have learned much from those that are intimate with Excel & support these boards ... Please know I appreciate the time you have taken to provide this guidance for one of my many Excel short-comings. My Thanks ... Have a Happy & Safe Day ... Kha "Trevor Shuttleworth" wrote: OK MAX(B3:P3) returns the maximum value in the range B3 to P3 MATCH(MAX(B3:P3),B3:P3,0) returns the offset of the maximum value within the range MATCH(MAX(B3:P3),B3:P3,0)+1 calculates the column number (taking into account we start in column B) ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) turns that into an address ... row 2, column whatever we just calculated =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) gets the value from the calculated address So, let's say the maximum value of 9 was in cell L3 which is column 12 =MAX(B3:P3) = 9 =MATCH(MAX(B3:P3),B3:P3,0) = MATCH(9,B3:P3,0) = 11 =MATCH(MAX(B3:P3),B3:P3,0)+1 = MATCH(9,B3:P3,0)+1 = 12 =ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) = ADDRESS(2, 12) = $L$2 =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) = INDIRECT($L$2) = column 12 or whatever Regards Trevor "Ken" wrote in message ... Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask T. Valko same thing ... His formula is flawless too. Thank you for the guidance ... Kha "Trevor Shuttleworth" wrote: One way: =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
Trevor ... (Happy morning)
Back at it again ... And "Yes" ... Without question, the intimate knowledge of Excel known by the Excel Magicians that support these boards is AWESOME. Thanks for the guidance ... Kha "Trevor Shuttleworth" wrote: Ken sorry, that would be a "no". Good though isn't it !? Best way is to Array Enter the formula into cell Q3 as suggested, then drag it across and look at how the formula changes in each column. You could also take any of the "inner" formulae and put them in a separate cell to evaluate them. Best I can suggest, I'm afraid. Perhaps Biff can be persuaded to clarify ? Regards Trevor "Ken" wrote in message ... Trevor ... (Happy morning) It has been a long week so I am just getting back to all of this ... Since I have had no further response from T. Valko & if you are still listening ... Would you be able to explain formula Valko provided in same way you explained yours (line item step by step) ... Not only is this helpful for me to better understand the specific functions, but also the combination of functions & order of calculations etc ... Formula is: =IF(COLUMNS($A:A)<=COUNTIF($B3:$P3,MAX($B3:$P3)),I NDEX($B$2:$P$2,SMALL(IF($B3:$P3=MAX($B3:$P3),COLUM N($B3:$P3)-MIN(COLUMN($B3:$P3))+1),COLUMNS($A:A))),"") Thank you in advance ... Kha "Trevor Shuttleworth" wrote: Thanks for the feedback. You have a great day too "Ken" wrote in message ... Trevor ... (Happy morning) I placed values in the applicable Range of a Blank WorkBook & worked this formula thru to help me better understand the Formulas & sequence of calculations (excellent explanation) ... I have learned much from those that are intimate with Excel & support these boards ... Please know I appreciate the time you have taken to provide this guidance for one of my many Excel short-comings. My Thanks ... Have a Happy & Safe Day ... Kha "Trevor Shuttleworth" wrote: OK MAX(B3:P3) returns the maximum value in the range B3 to P3 MATCH(MAX(B3:P3),B3:P3,0) returns the offset of the maximum value within the range MATCH(MAX(B3:P3),B3:P3,0)+1 calculates the column number (taking into account we start in column B) ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) turns that into an address ... row 2, column whatever we just calculated =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) gets the value from the calculated address So, let's say the maximum value of 9 was in cell L3 which is column 12 =MAX(B3:P3) = 9 =MATCH(MAX(B3:P3),B3:P3,0) = MATCH(9,B3:P3,0) = 11 =MATCH(MAX(B3:P3),B3:P3,0)+1 = MATCH(9,B3:P3,0)+1 = 12 =ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) = ADDRESS(2, 12) = $L$2 =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) = INDIRECT($L$2) = column 12 or whatever Regards Trevor "Ken" wrote in message ... Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask T. Valko same thing ... His formula is flawless too. Thank you for the guidance ... Kha "Trevor Shuttleworth" wrote: One way: =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| index,match,match on un-sorted data | Excel Worksheet Functions | |||
| INDEX / MATCH | Excel Worksheet Functions | |||
| Index/Match Help | Excel Worksheet Functions | |||
Linear Mode
