+ Reply to Thread
Results 1 to 9 of 9

Select max value in a row and pick the column header

  1. #1
    EK
    Guest

    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. #2
    Domenic
    Guest

    Re: Select max value in a row and pick the column header

    Try...

    E2, copied down:

    =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))

    Hope this helps!

    In article <[email protected]>,
    "EK" <[email protected]> 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. #3
    EK
    Guest

    Re: Select max value in a row and pick the column header

    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 <[email protected]>,
    > "EK" <[email protected]> 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. #4
    Jim May
    Guest

    Re: Select max value in a row and pick the column header

    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" <[email protected]> wrote in message
    news:[email protected]...
    > Try...
    >
    > E2, copied down:
    >
    > =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))
    >
    > Hope this helps!
    >
    > In article <[email protected]>,
    > "EK" <[email protected]> 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. #5
    Jim May
    Guest

    Re: Select max value in a row and pick the column header

    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" <[email protected]> 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> Try...
    >>
    >> E2, copied down:
    >>
    >> =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))
    >>
    >> Hope this helps!
    >>
    >> In article <[email protected]>,
    >> "EK" <[email protected]> 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. #6
    Dave Peterson
    Guest

    Re: Select max value in a row and pick the column header

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > Try...
    > >
    > > E2, copied down:
    > >
    > > =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))
    > >
    > > Hope this helps!
    > >
    > > In article <[email protected]>,
    > > "EK" <[email protected]> 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. #7
    Jim May
    Guest

    Re: Select max value in a row and pick the column header

    Thanks for the confirming dialog Dave.

    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Try...
    >> >
    >> > E2, copied down:
    >> >
    >> > =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))
    >> >
    >> > Hope this helps!
    >> >
    >> > In article <[email protected]>,
    >> > "EK" <[email protected]> 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. #8
    Domenic
    Guest

    Re: Select max value in a row and pick the column header

    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" <[email protected]>
    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > Try...
    > >
    > > E2, copied down:
    > >
    > > =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))
    > >
    > > Hope this helps!
    > >
    > > In article <[email protected]>,
    > > "EK" <[email protected]> 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. #9
    Jim May
    Guest

    Re: Select max value in a row and pick the column header

    Thanks Domenic, your comments clarify the Help "verbage".


    "Domenic" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]>
    > 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" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Try...
    >> >
    >> > E2, copied down:
    >> >
    >> > =INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0))
    >> >
    >> > Hope this helps!
    >> >
    >> > In article <[email protected]>,
    >> > "EK" <[email protected]> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1