I have a worksheet with the results of 52 golf matches throughout last year
for 50 players. I need to be able to show, for each player shown in column
A, the six highest scores achieved throughout the 52 weeks of the year (the
results for each players are entered in the 52 cells to the right of the
player's name). Some of the players have blanks in the cells when they did
not play or return a score. Is there a formula I can use such that each of
the top six results will appear in six separate columns in the corresponding
row for each player?

=LARGE($B$2:$B$100,COLUMNS($A:A))

Adjust to suit
Copy across the columns


"Jack M Taylor" wrote:

I have a worksheet with the results of 52 golf matches throughout last year
for 50 players. I need to be able to show, for each player shown in column
A, the six highest scores achieved throughout the 52 weeks of the year (the
results for each players are entered in the 52 cells to the right of the
player's name). Some of the players have blanks in the cells when they did
not play or return a score. Is there a formula I can use such that each of
the top six results will appear in six separate columns in the corresponding
row for each player?

Try something like this:

With
A2: (player name)
B2:BA2 contains the scores
and
BB1:BG1 contains the series 1 through 6

Then
This formula returns the highest score for the player on Row_2
BB2: =IF(COUNT($B$2:$BA$2)BB$1,LARGE($B$2:$BA$2,BB$1), "n/a")
Copy that formula across through BG2

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Jack M Taylor" wrote:

I have a worksheet with the results of 52 golf matches throughout last year
for 50 players. I need to be able to show, for each player shown in column
A, the six highest scores achieved throughout the 52 weeks of the year (the
results for each players are entered in the 52 cells to the right of the
player's name). Some of the players have blanks in the cells when they did
not play or return a score. Is there a formula I can use such that each of
the top six results will appear in six separate columns in the corresponding
row for each player?

Actually, the formula should be:
BB2: BB2: =IF(COUNT($B$2:$BA$2)=BB$1,LARGE($B$2:$BA$2,BB$1) ,"n/a")

The "" should be "="

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try something like this:

With
A2: (player name)
B2:BA2 contains the scores
and
BB1:BG1 contains the series 1 through 6

Then
This formula returns the highest score for the player on Row_2
BB2: =IF(COUNT($B$2:$BA$2)BB$1,LARGE($B$2:$BA$2,BB$1), "n/a")
Copy that formula across through BG2

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Jack M Taylor" wrote:

I have a worksheet with the results of 52 golf matches throughout last year
for 50 players. I need to be able to show, for each player shown in column
A, the six highest scores achieved throughout the 52 weeks of the year (the
results for each players are entered in the 52 cells to the right of the
player's name). Some of the players have blanks in the cells when they did
not play or return a score. Is there a formula I can use such that each of
the top six results will appear in six separate columns in the corresponding
row for each player?

Thanks Ron. Your solution worked a treat.

"Ron Coderre" wrote:

Actually, the formula should be:
BB2: BB2: =IF(COUNT($B$2:$BA$2)=BB$1,LARGE($B$2:$BA$2,BB$1) ,"n/a")

The "" should be "="

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try something like this:

With
A2: (player name)
B2:BA2 contains the scores
and
BB1:BG1 contains the series 1 through 6

Then
This formula returns the highest score for the player on Row_2
BB2: =IF(COUNT($B$2:$BA$2)BB$1,LARGE($B$2:$BA$2,BB$1), "n/a")
Copy that formula across through BG2

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Jack M Taylor" wrote:

I have a worksheet with the results of 52 golf matches throughout last year
for 50 players. I need to be able to show, for each player shown in column
A, the six highest scores achieved throughout the 52 weeks of the year (the
results for each players are entered in the 52 cells to the right of the
player's name). Some of the players have blanks in the cells when they did
not play or return a score. Is there a formula I can use such that each of
the top six results will appear in six separate columns in the corresponding
row for each player?