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!

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!

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!

Average score from repeating records?
 

Thanks!!! Great solutions and I've already implemented.

Average score from repeating records?
 

You are welcome! Thanks for the feedback!
Stefi

€žPtyrider€� ezt Ã*rta:

Thanks!!! Great solutions and I've already implemented.