Golf scores table

I have a simple database of two columns. Column 1 is the golfer's name and column 2 is the score for that round. There are about 50 golfers who have recorded 5 to 50 rounds each.

I want to create a table that lists the golfers in column 1, rounds played in column 2, average score in column 3, etc. I'm going nuts trying to figure this out. Thanks.

On sheet2

A1: = Sheet1!A1
A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$200&""),0)),"",
INDEX(IF(ISBLANK(Sheet1!$A$1:$A$200),"",Sheet1!$A$1:$A$200),MATCH(0,COUNTIF(
A$1:A1,Sheet1!$A$1:$A$200&""),0)))

as an array formula, so commit with Ctrl-Shift-Enter. Copy down however many
players that you have.

B1: =SUMPRODUCT(--(Sheet1!A1:A200=A1),--(Sheet1!A1:A200<>""))

and copy down

C1: =AVERAGE(IF(Sheet1!A1:A20=Sheet2!A1,Sheet1!B1:B20))

again an array formula, and again copy down.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TimH" <[email protected]> wrote in message
news:[email protected]...
>
> I have a simple database of two columns. Column 1 is the golfer's name
> and column 2 is the score for that round. There are about 50 golfers
> who have recorded 5 to 50 rounds each.
>
> I want to create a table that lists the golfers in column 1, rounds
> played in column 2, average score in column 3, etc. I'm going nuts
> trying to figure this out. Thanks.
>
>
> --
> TimH
> ------------------------------------------------------------------------
> TimH's Profile:
http://www.excelforum.com/member.php...o&userid=29131
> View this thread: http://www.excelforum.com/showthread...hreadid=488523
>

Have you considered a Pivot Table? Highlight your data then, from the menu
bar, Data > Pivot Table and Chart Report. Click 'Next' twice, then click
'Layout'. Drag 'Name' to the row area, and score to the data area TWICE.
Excel will default to 'Sum of Score' in the data area. Double click to edit.
Change the name (first time to '# Rounds', second time to 'Average') and
change the 'summarize by' function (first time to 'count', second time to
'average'). When the table is build, click on the cell labeled 'data', drag
it to the cell labeled 'total' and drop it.
One caveat about the Pivot Table: you'll have to explicitly refresh it when
your data changes (right-click in the table and select 'Refresh'). If you
add rows, you may need to invoke the Wizard as well to update the data range.
HTH. --Bruce

"TimH" wrote:

>
> I have a simple database of two columns. Column 1 is the golfer's name
> and column 2 is the score for that round. There are about 50 golfers
> who have recorded 5 to 50 rounds each.
>
> I want to create a table that lists the golfers in column 1, rounds
> played in column 2, average score in column 3, etc. I'm going nuts
> trying to figure this out. Thanks.
>
>
> --
> TimH
> ------------------------------------------------------------------------
> TimH's Profile: http://www.excelforum.com/member.php...o&userid=29131
> View this thread: http://www.excelforum.com/showthread...hreadid=488523
>
>

Your suggestions were spot on! Thanks!