Find max value in one column and return the value of corrosponding cell in different column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paul.mullan@hotmail.co.uk - 16 Oct 2007 11:24 GMT

Hi All,

Haveing a bad excel day - I am sure I have done this before but
racking my brains and can't remember!!

I have a list of scores for various tests listed by agegroup. I am
trying to summarise the data on a seperate worksheet to list who
attained the maximum value in each test for each agegroup. So using
the example below of the raw data I am trying to use a function to
find the MAX of T1 for age 11 (22) and age 12 (10). What I am trying
to do is use a function to list the owner of these MAX values listed
in column B

A B C D
Age Name T1 T2
11 George 10 5
11 Dave 15 2
11 Jim 22 1
12 Paul 5 8
12 James 4 10
12 Dylan 10 11

Can I apply a similar function to list the 'owner' of the max value?

Thanks in advance
Paul

Reply to this Message

paul.mullan@hotmail.co.uk - 16 Oct 2007 11:54 GMT

Ah, I think I did something similar using the OFFSET function
before . . .

I have tried using this combined with the MAX function to list the
reference value. An example of what I have tried in relation to the
data above would be. However I am told my formula contains an error.

=OFFSET(MAX(C2:C4),0,-1,1,1)

I might be going down completely the wrong road! Any suggestions would
be gratefully received

Cheers
Paul

Reply to this Message

vezerid - 16 Oct 2007 11:55 GMT

Assuming the table starts at A1 (Age label) and occupies cells A1:D7.
A1:D1 contain headers.

A11 contains 11, A12 contains 12, B10 contains T1, C10 contains T2
(cross tabulation)

In B11, *array* formula (commit with Shift+Ctrl+Enter)

=INDEX($B$2:$B$7,MATCH(MAX(IF($A$2:$A$7=$A11,INDEX($A$2:$D$7,0,MATCH(B
$10,$A$1:$D$1,0)))),IF($A$2:$A$7=$A11,INDEX($A$2:$D$7,0,MATCH(B$10,$A
$1:$D$1,0)),0),0))

Copy through the range B11:C12

HTH
Kostis Vezerides

On Oct 16, 1:24 pm, paul.mul...@hotmail.co.uk wrote:

> Hi All,
>
[quoted text clipped - 22 lines]
> Thanks in advance
> Paul

Reply to this Message

Max - 16 Oct 2007 11:55 GMT

One way

Array-entered (press CTRL+SHIFT+ENTER):
=INDEX(B2:B7,MATCH(MAX(IF(A2:A7=11,C2:C7)),IF(A2:A7=11,C2:C7),0))
will return the name: Jim, who has the max score for age group: 11 for T1

Note that in the event of any ties in the max score, the expression will
return the first name (the one higher up in the list)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi All,
>
[quoted text clipped - 22 lines]
> Thanks in advance
> Paul

Reply to this Message

Roger Govier - 16 Oct 2007 12:02 GMT

Hi Paul

I entered in G1 "T1" and H1 "T2" (without the quotes)
In F2 I entered 11 and in F3 12.

The array entered formula in G2
{=MAX(IF($B$2:$B$7=$F2,D$2:D$7,""))}
copied to H2 and G3:H3
returned the maximum values for each age group and each test.

Array formulae are created and edited using Control+Shift+Enter (CSE) not
just Enter.
When you use CSE, Excel will create the curly braces { } around the
formula. Do not type them yourself.

In G5 enter (normal enter, not array)
=INDEX($C$2:$C$7,MATCH(G2,D$2:D$7,0))
and copy to H5, G6:H6
to return the corresponding names

Signature

Regards
Roger Govier

> Hi All,
>
[quoted text clipped - 22 lines]
> Thanks in advance
> Paul

Reply to this Message

Sandy Mann - 16 Oct 2007 12:33 GMT

Just another option:

Age 11:

=INDEX(B2:B7,MATCH(MAX((A2:A7=11)*C2:C7),(A2:A7=11)*C2:C7))

Age 12:

=INDEX(B2:B7,MATCH(MAX((A2:A7=12)*C2:C7),(A2:A7=12)*C2:C7))

As witht the others, entered as an array formula with Ctrl + Shift + Enter

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Hi All,
>
[quoted text clipped - 22 lines]
> Thanks in advance
> Paul

Reply to this Message