Average For Each Occurance In a Row

Greetings, all. I'm new to the forum and have a specific question that is driving me nuts. I would like to sort a spreadsheet by column A, then apply a drag and drop formula that will return the average value of the data in column B for each grouping is column A.

Put differently, take the example below. I want Excel to tell me the average value of the data in column B for the total occurances of 34 in column A, or 10.67. I am working on spreadsheets with 15,000+ columns, comprised of 7,000 - 8,000 uniuque values in column A (some only occur once, some 3+x).

Any help would be appreciated!!!!

Chuck

A B C?
34 $10
34 $15
34 $7
18 $44
18 $22
16 $9
16 $84
16 $44

With your sorted data starting in A2:B2, this formula in C2 and copied down:

=IF(A3=A2,"",AVERAGEIF(A$2:A2,A2,B$2:B2))

You are an Excel NINJA! That worked per-fect-ly! Much appreciated. I'm not yet sure what the commands represent, but I'll educate myself on that later.

Regards,
Chuck

Glad it worked for you. Please remember to mark your thread as SOLVED (see instructions in rule #9 by clicking Forum Rules button @ top of page).

Hello,

I would use a Pivot Table.

For example select cell D1 Go to the Insert tab and click Pivot Table.

Select your range Sheet1!$A$1:$B$9 and click OK.
Then in the right hand pane, pull column A into the Row labels section and the B column in to the Values section.

Click on the Sum of B in the Values section and select Value Field Settings then Average.

I hope this helps.

Cheers,
Diana