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.
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.
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.
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.
>
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.
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.
>
>
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
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
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks