first ten and last ten averages
 

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).

first ten and last ten averages
 

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---
--
RyGuy


"aazharr" wrote:

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).

first ten and last ten averages
 

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:B300 0)/SUMPRODUCT((A2:A3000=D2)*(C2:C3000="LOW"))
Average of last ten
=SUMPRODUCT((A2:A3000=D2)*(C2:C3000="HIGH")*B2:B30 00)/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


"aazharr" wrote in message
...
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).