Average if in same group

G

ganesh

Hello,
I'd appreciate some help with an excel problem. I have 2 columns of
data as follows:

c1 c2
a 2
b 3
a 3
c 3
d 2
a 5
c 2

I want to take the average of all values corresponding to "a", all
values in group "b", etc. I've come across the formula:

=AVERAGE(IF($A$1:$A$7="a",B1:B7)) on the web.

Unfortunately, the search term is hard coded. I have a dataset that
consists of literally 1000s of groups. Therefore, it would be best if
excel could go through the first column, find the groups, average the
group's values, and then print out the group and its' average in 2
additional columns,thus:


c1 c2 c3 c4
a 2 a 3.33
b 3 b 3
a 3 c 2.5
c 3 d 2
d 2
a 5
c 2

Thanks in advance for your help!
 
B

Biff

A non-pivot table alternative:

Use an advanced filter to create the list in col 3 of
unique values from col A. Then just use your formula but
change the hard coded reference to a reference to the
values in col 3.

=AVERAGE(IF($A$1:$A$7=C1,B1:B7))

Biff
 
Top