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

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

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, wrote:
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

One way

Array-entered (press CTRL+SHIFT+ENTER):
=INDEX(B2:B7,MATCH(MAX(IF(A2:A7=11,C2:C7)),IF(A2:A 7=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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
oups.com...
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

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
--
Regards
Roger Govier



wrote in message
oups.com...
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

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

--
HTH

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


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


wrote in message
oups.com...
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