first ten and last ten averages

[aazharr]

in one column i have names and in other column i have values. each same name
can have lot of values
so in column A all the names are written , (one name can have more than one
values). and in colum B i have all the values . I want a fuction which gives
me
1. average of first ten , and last ten values of one name .(in two seprate
cells)
2.average of all the values of one name (in one seperate cell).

 

[ryguy7272]

Try this, with names in Column A, and numbers in Column B:
=AVERAGE(IF(A1:A20="Bill",(B1:B20)))
Ctrl+Shift+Enter

Also, average of top 10:
=AVERAGE(LARGE(B1:B20,{1,2,3,4,5,6,7,8,9,10}))
Ctrl+Shift+Enter


Regards,
Ryan---

 

[Bernie Deitrick]

aazharr,

In cell C2, enter the formula
=IF(COUNTIF($A$2:A2,A2)<=10,"LOW",IF(COUNTIF($A$2:A2,A2)>COUNTIF(A:A,A2)-10,"HIGH",""))
and copy down to match your list.

Then, with the name in D2, use these three formulas - change all the instances of 3000 to reflect
your last row number
Average of first ten:
=SUMPRODUCT((A2:A3000=D2)*(C2:C3000="LOW")*B2:B3000)/SUMPRODUCT((A2:A3000=D2)*(C2:C3000="LOW"))
Average of last ten
=SUMPRODUCT((A2:A3000=D2)*(C2:C3000="HIGH")*B2:B3000)/SUMPRODUCT((A2:A3000=D2)*(C2:C3000="HIGH"))
Average of all:
SUMIF(A:A,D2,B:B)/COUNTIF(A:A,D2)

HTH,
Bernie
MS Excel MVP