Probably an easy one...

[mykdogg241]

I have a list of basketball players and their stats(points,reb,ast,steals,etc.)
On sheet2 I want to have the 'leaders'(points leader,reb leader). I can determine this easy enough(MAX()) but how do I then associate the number with the player automatically as the totals change?
example:
POINTS LEADER
name 1,000 points

[Yogi Anand]

On 2002-03-23 10:11, mykdogg241 wrote:
I have a list of basketball players and their stats(points,reb,ast,steals,etc.)
On sheet2 I want to have the 'leaders'(points leader,reb leader). I can determine this easy enough(MAX()) but how do I then associate the number with the player automatically as the totals change?
example:

POINTS LEADER
name 1,000 points

Hi mykdogg241:
What do you mean by associating the numbers with the players ... your calculation of maximum scores will be fed from the players stats anyway ... a little clarification of what you are trying to do ... and then let us take it from there!

[mykdogg241]

example: 3 columns
players pts reb
john 20 5
steve 30 7
ralph 40 2


on sheet2 I can determine the highest point total on column 2"pts" = 40 MAX(B2:B4)
How do I also associate ralph with his 40 points automatically so it looks like this:
POINTS LEADER
Ralph 40
Say the totals were updated so john has 50 points, sheet2 should automatically change to
POINTS LEADER
John 50

[Yogi Anand]

On 2002-03-23 11:09, mykdogg241 wrote:
example: 3 columns
players pts reb
john 20 5
steve 30 7
ralph 40 2


on sheet2 I can determine the highest point total on column 2"pts" = 40 MAX(B2:B4)
How do I also associate ralph with his 40 points automatically so it looks like this:
POINTS LEADER
Ralph 40
Say the totals were updated so john has 50 points, sheet2 should automatically change to
POINTS LEADER
John 50

Hi mykdogg241:
Here is the formula ...
="Points Leader "&UPPER(INDEX(A2:A4,MATCH(MAX(B2:B4),B2:B4,0),1))&" "&MAX(B2:B4)

The MATCH function computes the row number in which the associated max score of 40 lies --
The index function then uses the row number found by the MATCH function to zero in on RALPH

if you then change John's score to 50, it gives:

Points Leader JOHN 50

HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

_________________
Regards!
Yogi Anand

Modified hard coded signature to Edit out inactive website

[ This Message was edited by: Yogi Anand on 2003-01-19 12:35 ]

[Aladin Akyurek]

On 2002-03-23 11:09, mykdogg241 wrote:
example: 3 columns
players pts reb
john 20 5
steve 30 7
ralph 40 2


on sheet2 I can determine the highest point total on column 2"pts" = 40 MAX(B2:B4)
How do I also associate ralph with his 40 points automatically so it looks like this:
POINTS LEADER
Ralph 40
Say the totals were updated so john has 50 points, sheet2 should automatically change to
POINTS LEADER
John 50

That's the easy part: the trouble is that you can have more names who have the MAX value as their score. What follows is going to sound complicated, but it should do what you want.

In Sheet1 we have the sample data (with one more row added) you provided in A1:C5.
Take notice that the actual data starts at row 2.

In Sheet2:

in A2 enter:

=MATCH(9.99999999999999E+307,Sheet1!B:B)-ROW(Sheet1!1:1)

in B2 enter:

=MAX(OFFSET(Sheet1!B2,0,0,A2,1))

You can hide columns A and B, if desired.

In C2 enter:

=IF($B$2,INDEX(OFFSET(Sheet1!$A$2,0,0,$A$2,1),MATCH($B$2,OFFSET(Sheet1!$B$2,0,0,$A$2,1),0)),"")

in C3 enter and drag down untill no more names appear:

=IF(AND($B$2,COUNTIF(OFFSET(Sheet1!$B$2,0,0,$A$2,1),$B$2)>COUNTA($C$2:C2)),INDEX(OFFSET(Sheet1!$A$2,MATCH(C2,OFFSET(Sheet1!$A$2,0,0,$A$2,1),0),0,$A$2,1),MATCH($B$2,OFFSET(Sheet1! $B$2,MATCH(C2,OFFSET(Sheet1!$A$2,0,0,$A$2,1),0),0,$A$2,1),0)),"")

That's the result we get:

{"ralph";
"aladin"}

Aladin



[ This Message was edited by: Aladin Akyurek on 2002-03-23 12:08 ]

[mykdogg241]

Works great! Thanks

[Jay Petrulis]

On 2002-03-23 11:09, mykdogg241 wrote:
example: 3 columns
players pts reb
john 20 5
steve 30 7
ralph 40 2


on sheet2 I can determine the highest point total on column 2"pts" = 40 MAX(B2:B4)
How do I also associate ralph with his 40 points automatically so it looks like this:
POINTS LEADER
Ralph 40
Say the totals were updated so john has 50 points, sheet2 should automatically change to
POINTS LEADER
John 50

If you are trying to use only one dataset and find the individual leaders as game totals are added (i.e John 20 pts 1st game, 30 pts second), then it will be exceedingly difficult, unless I am missing something obvious.

Names in col A, Pts in column B, this returns the leading scorer:

=MAX(SUMIF(A1:A15,"John",B1:B15),SUMIF(A1:A15,"Ralph",B1:B15),SUMIF(A1:A15,"Steve",B1:B15))

More names and this will get very long. Named formulas might help.

My recommendation would be to carry two tables. One is the player roster, where you can grab all the individual stats in that table by referencing the data set (the second table).

Then, The leader columns can be determined from the player table.

By the way, what's with Ralph? 40 points and only two rebounds? Only a gunner? I hope he plays some defense.

Bye,
Jay