Variable number of multiple rows per record; want summary by record

[TR]

Here's what the data look like

Pat 10 20
Joe 15 10
Joe 8 11
Kay 9 32
Pat 14 17
Joe 33 11

In short, each person has from 1 to n rows of data.. I want a summary
(probably on a different sheet) that shows the mean for each person.
The new sheet will have one row per person, showing the mean for each
data column for that person.

Suggestions?

TIA

 

[jpdphd]

TR
It looks like the sort of thing a pivot table could handle. It isn't
clear, tho, what the two columns after the names represent. Are they
different things (Wins and Losses) or the same (Bananas)?

Assuming that they are different, give each column a header. (eg Name,
Win, Loss)
Select all rows & columns with data (including header)
Data>PivotTable Report...
Step 1 - where is data? choose Excel list or database
Step 2 - should already show the range containing the data
Step 3 - where to put table, choose New Worksheet
click on Layout
Drag the "Name" label to the Row box
Drag the "Win" label to the Data box. It will say "sum of Win". Double
click "sum of Win"
Choose Average instead of sum. Format if you want.
Do same with "Loss".
Ok. Finish.

There are more bells and whistles, but this is the basic idea.
Just don't trust the "standard deviation" option (see my recent post).

If ColB and ColC are really the same sort of quantity (bananas), it can
still be done, but is a bit trickier. You have to display COUNT of each
column in the pivot table and do some math to find the overall average.
Too complicated for this post.

jpdphd

 

[JE McGimpsey]

In short, each person has from 1 to n rows of data.. I want a summary
(probably on a different sheet) that shows the mean for each person.
The new sheet will have one row per person, showing the mean for each
data column for that person.

One way (array-entered: CMD-RETURN):

=AVERAGE(IF($A1:$A100="Pat",B1:B100))

another (entered normally):

=SUMIF($A1:$A100,"Pat",B1:B100)/COUNTIF($A1:$A100,"Pat")

 

[TR]

Thanks to both of you for suggestions. I like the array-entered
solution-- does just what I need and easily.

Next question (there's always another).

If there were an easy way to take the column containing the names and
generate a new column that contains only the unique names from the
list, my life would get very easy.

So, given

Pat
Joe
Carol
Joe
Joe
Pat
Rick

I want a column containing

Pat
Joe
Carol
Rick

Again, TIA.

TR

 

[JE McGimpsey]

If there were an easy way to take the column containing the names and
generate a new column that contains only the unique names from the
list, my life would get very easy.

Data/Filter/Advanced Filter. Click the Copy to another location radio
button. Enter the Copy To range, and check the Unique records only
checkbox. Click OK.