Average by category

[Sacrebleurgh]

The title isn't very good I know; I don't know the right terminology to explain the problem properly, but i'll give it a go:

In column A I have a list of names, all of them appear multiple times. In column B there is a corresponding percentage. I would like to create a formula that takes the average of all the percentages that have the same corresponding name, or 'category'. I don't want to just go through and do it manually since there are roughly 32,000 different values.

I did have one idea; in column C I pasted in a list of all the possible names in column A and then in column D I used countif to find out how many times each name recurred. If both columns A and C are in alphabetical order, I thought I could use the normal average function, by doing, for example AVERAGE(B1:BD1), but this didn't work. Is there some special way in which you can use a cell name (i.e. D1) instead of a number in a cell range? I tried brackets, speech marks etc. but nothing worked.

I have searched around a bit on this forum to see if my question had already been answered, and did some extensive googling, but I couldn't find a solution.

Thanks for your help! I'm sorry my first post is asking for help; i'll stick around afterwards and try and help out where I can.

[arthurbr]

I in col C you have all possible names you might try in D1
=average(if($a$1:$a$100=$c1,$b$1:$b$100,false)) entered as an array formula ( with Ctrl+Shift+Enter) and pull down as needed.
Adapt the ranges as needed also
( if necessary post a small sample of your data)

[Sacrebleurgh]

Thanks for the quick reply, the formula worked perfectly, i'm not sure how or why, but it solved my problem!

[arthurbr]

Glad to help

[Tlwiersum]

I'm having the same problem, and must be making a mistake somewhere because this formula isn't working for me. I have included a jpeg of a simplified version of my data below.

Example data.JPG

Basically, I have a large number of observations that each have a corresponding SIC code. Each observation is a value for a firm's performance. Next to these columns (A and B) I have a list of all possible SIC codes in C. In D I would like to calculate the average value per category, or possible value, of the SIC codes.

[Tlwiersum]

I've worked the problem out!