Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averages | Excel Discussion (Misc queries) | |||
averages without zero | Excel Worksheet Functions | |||
Averages | New Users to Excel | |||
averages | Excel Worksheet Functions | |||
averages | Excel Discussion (Misc queries) |