Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average score from repeating records?
So I have a list of IDs (repeating) and scores like this:
A B 4710051 3 4710051 3 4710064 4 4710064 2 4710070 4 4710070 5 4710070 4 I make a list of unique IDs in col. C. And then I need a formula or VBA routine to get the average score and put it in col. D. C D 4710051 3 4710064 3 .. .. .. Before (thanks to this group) I could get the MAX, like this: =SUMPRODUCT(MAX(($A$2:$A$5988=$c2)*$B$2:$B$5988)) But I can't just substitute AVERAGE() for MAX, so don't know what to do. Help appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average score from repeating records?
=AVERAGE(IF(A1:A100=C1,B1:B100))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ptyrider" wrote in message ups.com... So I have a list of IDs (repeating) and scores like this: A B 4710051 3 4710051 3 4710064 4 4710064 2 4710070 4 4710070 5 4710070 4 I make a list of unique IDs in col. C. And then I need a formula or VBA routine to get the average score and put it in col. D. C D 4710051 3 4710064 3 . . . Before (thanks to this group) I could get the MAX, like this: =SUMPRODUCT(MAX(($A$2:$A$5988=$c2)*$B$2:$B$5988)) But I can't just substitute AVERAGE() for MAX, so don't know what to do. Help appreciated! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average score from repeating records?
=SUMIF(A:A,C2,B:B)/COUNTIF(A:A,C2)
Regards, Stefi €˛Ptyrider€¯ ezt Ć*rta: So I have a list of IDs (repeating) and scores like this: A B 4710051 3 4710051 3 4710064 4 4710064 2 4710070 4 4710070 5 4710070 4 I make a list of unique IDs in col. C. And then I need a formula or VBA routine to get the average score and put it in col. D. C D 4710051 3 4710064 3 .. .. .. Before (thanks to this group) I could get the MAX, like this: =SUMPRODUCT(MAX(($A$2:$A$5988=$c2)*$B$2:$B$5988)) But I can't just substitute AVERAGE() for MAX, so don't know what to do. Help appreciated! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average score from repeating records?
Thanks!!! Great solutions and I've already implemented.
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average score from repeating records?
You are welcome! Thanks for the feedback!
Stefi €˛Ptyrider€¯ ezt Ć*rta: Thanks!!! Great solutions and I've already implemented. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make a sheet to enter score and score is copy to the proper cell.. | Excel Worksheet Functions | |||
score | Excel Discussion (Misc queries) | |||
How can I average data from a repeating list into a unique list? | Excel Worksheet Functions | |||
How can I average data from a repeating list into a unique list? | Excel Worksheet Functions | |||
Average Grades and drop the lowest score | Excel Worksheet Functions |