Average if in same group

[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!

 

[Ryan Poth]

Ganesh,

The best way would be to use a pivot table.

Ryan

 

[Myrna Larson]

A Pivot Table will do just this for you. Check it out on the Data menu.

 

[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