How can I average data from a repeating list into a unique list?

H

Hicoda

I have a list of twenty five names. This list contains data from 1 month.
Each name has multipule occurances. I need to create a list of unique names
with the average of each individules data.
 
D

Duke Carey

Use a pivot table. Select any cell in your table, then go to Data->Pivot
table and click on Finish. In the diagrm that comes up, drag the Names to
the row area and the data to the data area. Right click on the data field
name and choose average
 
B

Bob Phillips

In E1: =A1
E2: =IF(ISERROR(MATCH(0,COUNTIF(E$1:E1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(E$1:E1,$A$1:$A$2
0&""),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy E2 down as far as you might need, max E25

F1: =IF(E1="","",AVERAGE(IF(A1:A25=E1,B1:B25)))

also an array formula. Copy this down to the same row as in E.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top