Golf scores table

[TimH]

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.

 

[Bob Phillips]

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)

 

[Guest]

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]

Your suggestions were spot on! Thanks!