Average of Column B Bases on Column A

[jennh]

Oh dear...first of all, I apologize for not having an appropriate title as I don't know what words to use! But...here is my problem. I have a very large dataset with various columns, but I am only interested in two of them: "SampleID" and "Value". I would like Excel to search through the SampleID column and find cells with the same ID, then calculate the average of the "Value" column based on those search results, and return the results in another column.

For example, in the end I would like my spreadsheet to look like this (without the dots, of course):

SampleID.....Value.....AvgValue
351268........50
351268........75
351268........75.........100
362999........20
362999.........4..........12
375555.........60.........60

I hope I have been able to get my question across and I thank you in advance for reading this and trying to help.

Cheers

 

[cjcobra]

you could use a pivot table.

 

[Aladin Akyurek]

One of...

1]

C2, control+shift+enter, not just enter, and copy down:

=AVERAGE(IF($A$2:$A$7=A2,$B$2:$B$7))

If on Excel 2007 or later...

=AVERAGEIF($A$2:$A$7,A2,$B$2:$B$7)

2] If you want to see the average at the last occurrence of a condition...

Control+shift+enter, not just enter, and copy down:

=IF(A2=LOOKUP(9.99999999999999E+307,1/($A$2:$A$7=A2),$A$2:$A$7),
    AVERAGE(IF($A$2:$A$7=A2,$B$2:$B$7)),"")