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

Okay, let's pretend A1:A800000 is your range.

Copy that column, paste over in D. Select your new range D1:D800000, go to Data -> Remove Duplicates, and hit okay.

Now you have your unique values to build off of. From here, there's a number of ways you can proceed.


=AVERAGEIF(criteria range, criteria, average range)


So, for E1:
=AVERAGEIF($A$1:$A$800000,D1,$B$1:$B$800000) and copy down

This will average column B where Column A equals whats in D1.


Another example would be to use SUMPRODUCT, though a bit longer.

E1:
=SUMPRODUCT(($A$1:$A$800000=D1)*($B$1:$B$800000))/SUMPRODUCT(--($A$1:$A$800000=D1))

This is the SUM of Column B where Column A equals D1, divided by the number of times Column A equaled D.

No problem, glad I could help.




If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Or try the use of a pivot table