Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF for an AVERAGE
I have a rather complex (at least to me) question... My worksheet has column
D with agent initials and column AE with a score. I'm trying to figure out the formula to calculate an average score for each agent in column D based on amount in AE? Column D Column AE AMJ 5 CLY 10 KRR 2 AMJ 5 KRR 3 DZL 2 etc., etc. Sometimes I'll have 100+ rows of information. Any help is appreciated! Tia B |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF for an AVERAGE
One way:
Assume you have the unique agents listed in the range A2:A5 Enter this formula in B2 and copy down to B5: =SUMIF(D$2:D$7,A2,AE$2:AE$7)/MAX(1,COUNTIF(D$2:D$7,A2)) Biff "TiaB" wrote in message ... I have a rather complex (at least to me) question... My worksheet has column D with agent initials and column AE with a score. I'm trying to figure out the formula to calculate an average score for each agent in column D based on amount in AE? Column D Column AE AMJ 5 CLY 10 KRR 2 AMJ 5 KRR 3 DZL 2 etc., etc. Sometimes I'll have 100+ rows of information. Any help is appreciated! Tia B |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF for an AVERAGE
One way ..
Source data in cols D and AE, assumed from row1 down, where col D = agents col AE = scores In AG1: =IF(D1="","",IF(COUNTIF($D$1:D1,D1)1,"",ROW())) In AH1: =IF(ROW()COUNT(AG:AG),"",INDEX(D:D,SMALL(AG:AG,RO W()))) Select AG1:AH1, copy down to the max expected extent of source data, eg copy down to AH200. This drives out a unique list of agents into col AH. with all results neatly bunched at the top. Hide away col AG1 if desired. Then to get the average for each agent in col AH, place in AI1 and array-enter the formula, ie press CTRL+SHIFT+ENTER, instead of just pressing ENTER: =AVERAGE(IF($D$1:$D$200=AH1,$AE$1:$AE$200)) Copy AI1 down as far as required. (Another way would be to use a pivot table) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TiaB" wrote: I have a rather complex (at least to me) question... My worksheet has column D with agent initials and column AE with a score. I'm trying to figure out the formula to calculate an average score for each agent in column D based on amount in AE? Column D Column AE AMJ 5 CLY 10 KRR 2 AMJ 5 KRR 3 DZL 2 etc., etc. Sometimes I'll have 100+ rows of information. Any help is appreciated! Tia B |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average/Sumif based on several columns | Excel Worksheet Functions | |||
Average/Sumif based on several columns | Excel Worksheet Functions | |||
Weighted average using SUMIF and/or SUMPRODUCT | Excel Discussion (Misc queries) | |||
Excluding Zero's from Average (SumIF / CountIF) | Excel Worksheet Functions | |||
Using VLookup, Displacement, SumIF to total and average | Excel Discussion (Misc queries) |