Finding max value from group of variables

[mynameiswills]

Hi,

first time post

I have data that looks like this:

A B C D E F

1 2 3 4 5 6

2 3 4 5 8 1

3 5 5 3 4 7

I want to define the max value on each row, ideally giving the name of the variable not just the max value. So it would look like this:

A B C D E F G

1 2 3 4 5 6 F

2 3 4 5 8 1 E

3 5 5 3 4 7 F

Ive tried unsuccessfully to do so. Is this even possible?

Thanks in advance

[Jonmo1]

Try

=INDEX($A$1:$F$1,MATCH(MAX($A2:$F2),$A2:$F2,0))

Where A1:F1 is the headers (the row containing the 'names' you want the result to be)
A2:F2 is the row with the numbers you want to find the max value of.

[JohnTopley]

Try in G2

=INDEX($A$1:$F$1,MATCH(MAX($A2:$F2),$A2:$F2,0))


data in A1 to F4 (row 1 Headings)

[mynameiswills]

Hi,

thanks that worked a treat.

just another quick question, when there are two 'winners' this formula arbitrarily selects the first value as the 'winner'. Is there any way of remedying this so that it shows as a draw?

[samba_ravi]

=IF(COUNTIF($A2:$F2,MAX($A2:$F2))>1,"Draw",LOOKUP(2,1/($A2:$F2=MAX($A2:$F2)),$A$1:$F$1))

Please Login or Register  to view this content.

Try this and copy towards down

[mynameiswills]

this works as well but there is an issue with dividing by 0 given that some of the responses are 0. dont mean to keep asking more questions, its just the formula is a bit above my excel capabilities to modify

[JohnTopley]

I tried the response fom Nflsales and had no problem with either a line of Os or o in the data, Can you post a sample file showing the problem you are encountering.

Click "Go advanced" then Paper Clip Icon or Manage Attachments to upload a file,