Average column based on another columns value.

Let's pretend your data starts in A1, and you got the things you care about in C1.

D1

=AVERAGEIFS($C$1:$C$5,$B$2:$B$5,B2) and copy down the side

Hey so in the interest of learning, what exactly in that formula denotes that it is to be average if a duplicate is present?

Also, there are about 800,000 values. Does the scale influence how this should be done?


EDIT: Did as you asked and it only seems to find a new average. I would like it to a) collapse all the duplicates and then instead present the average in a particular column.

a 1
a 1
a 1
a 1
a 1
b 1
c 1
d 1
e 1
e 1
e 1

becomes

a 1
b 1
c 1
d 1
e 1