Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Select max value in a row and pick the column header
A B C D Result 1 2 3 4 D 1 2 4 3 C 3 1 2 4 D 4 3 2 1 A I would like to know the formula to get Result. |
#2
|
|||
|
|||
Try...
E2, copied down: =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) Hope this helps! In article , "EK" wrote: A B C D Result 1 2 3 4 D 1 2 4 3 C 3 1 2 4 D 4 3 2 1 A I would like to know the formula to get Result. |
#3
|
|||
|
|||
Yes it does!
Thanks. "Domenic" wrote: Try... E2, copied down: =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) Hope this helps! In article , "EK" wrote: A B C D Result 1 2 3 4 D 1 2 4 3 C 3 1 2 4 D 4 3 2 1 A I would like to know the formula to get Result. |
#4
|
|||
|
|||
How is it that we test using the row argument only (getting the maximum) and
Excel returns the column letter; and we give the column argument 0 (zero)..??? programming,, go figure... "Domenic" wrote in message ... Try... E2, copied down: =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) Hope this helps! In article , "EK" wrote: A B C D Result 1 2 3 4 D 1 2 4 3 C 3 1 2 4 D 4 3 2 1 A I would like to know the formula to get Result. |
#5
|
|||
|
|||
Been studying this more since posting, and I suppose the formula is of the
"array-type" Index() and according to help, if the 1st argument is a single row or column, then row number (Max()) produces the array-element number in the index (arg1); Also, besides, the last 0 (zero) I originally took to refer to the column argument, when in fact it is the 3rd part of the row argument (the Match_Type).. Sorry, for the "false-alarm"; I think I got it now. "Jim May" wrote in message news:5VLDe.80813$Fv.9465@lakeread01... How is it that we test using the row argument only (getting the maximum) and Excel returns the column letter; and we give the column argument 0 (zero)..??? programming,, go figure... "Domenic" wrote in message ... Try... E2, copied down: =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) Hope this helps! In article , "EK" wrote: A B C D Result 1 2 3 4 D 1 2 4 3 C 3 1 2 4 D 4 3 2 1 A I would like to know the formula to get Result. |
#6
|
|||
|
|||
The formula:
=INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) Is return something from A1:D1 (the headers) It's gonna find the largest value in A2:D2 with the max() function. And look into A2:D2 to find what column in that range has the maximum (the 0 means it has to be an exact match). =Match() will return a 1, 2, 3, 4, ... depending on where the match was found. (1 for first element, 2 for second element, ....) So =index(a1:d1,#) will return the value in the #th position of that range (a1:d1). ====== If you plop some test data into a worksheet and then put the formula in another cell, you can highlight each piece of the formula and hit f9 to see what it evaluates to. =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) select max(a2:d2) and hit f9 select match(----0) and hit f9 select all of it and hit f9. ======= In later versions of excel (xl2002+), you can use tools|formula auditing|evaluate formula to do the same thing. Jim May wrote: How is it that we test using the row argument only (getting the maximum) and Excel returns the column letter; and we give the column argument 0 (zero)..??? programming,, go figure... "Domenic" wrote in message ... Try... E2, copied down: =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) Hope this helps! In article , "EK" wrote: A B C D Result 1 2 3 4 D 1 2 4 3 C 3 1 2 4 D 4 3 2 1 A I would like to know the formula to get Result. -- Dave Peterson |
#7
|
|||
|
|||
Thanks for the confirming dialog Dave.
"Dave Peterson" wrote in message ... The formula: =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) Is return something from A1:D1 (the headers) It's gonna find the largest value in A2:D2 with the max() function. And look into A2:D2 to find what column in that range has the maximum (the 0 means it has to be an exact match). =Match() will return a 1, 2, 3, 4, ... depending on where the match was found. (1 for first element, 2 for second element, ....) So =index(a1:d1,#) will return the value in the #th position of that range (a1:d1). ====== If you plop some test data into a worksheet and then put the formula in another cell, you can highlight each piece of the formula and hit f9 to see what it evaluates to. =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) select max(a2:d2) and hit f9 select match(----0) and hit f9 select all of it and hit f9. ======= In later versions of excel (xl2002+), you can use tools|formula auditing|evaluate formula to do the same thing. Jim May wrote: How is it that we test using the row argument only (getting the maximum) and Excel returns the column letter; and we give the column argument 0 (zero)..??? programming,, go figure... "Domenic" wrote in message ... Try... E2, copied down: =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) Hope this helps! In article , "EK" wrote: A B C D Result 1 2 3 4 D 1 2 4 3 C 3 1 2 4 D 4 3 2 1 A I would like to know the formula to get Result. -- Dave Peterson |
#8
|
|||
|
|||
As per Excel's help file...
"If array contains only one row or column, the corresponding row_num or column_num argument is optional." So, in this case, since we only have one row, the row_num argument is omitted and MATCH(MAX(...)) is used for the column_num argument. Hope this helps! In article <5VLDe.80813$Fv.9465@lakeread01, "Jim May" wrote: How is it that we test using the row argument only (getting the maximum) and Excel returns the column letter; and we give the column argument 0 (zero)..??? programming,, go figure... "Domenic" wrote in message ... Try... E2, copied down: =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) Hope this helps! In article , "EK" wrote: A B C D Result 1 2 3 4 D 1 2 4 3 C 3 1 2 4 D 4 3 2 1 A I would like to know the formula to get Result. |
#9
|
|||
|
|||
Thanks Domenic, your comments clarify the Help "verbage".
"Domenic" wrote in message ... As per Excel's help file... "If array contains only one row or column, the corresponding row_num or column_num argument is optional." So, in this case, since we only have one row, the row_num argument is omitted and MATCH(MAX(...)) is used for the column_num argument. Hope this helps! In article <5VLDe.80813$Fv.9465@lakeread01, "Jim May" wrote: How is it that we test using the row argument only (getting the maximum) and Excel returns the column letter; and we give the column argument 0 (zero)..??? programming,, go figure... "Domenic" wrote in message ... Try... E2, copied down: =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)) Hope this helps! In article , "EK" wrote: A B C D Result 1 2 3 4 D 1 2 4 3 C 3 1 2 4 D 4 3 2 1 A I would like to know the formula to get Result. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|