Search and give average

[Ph4r40h]

Ok, so I need a little help. I have a Table similar to this:

VA OAKTON NA
CA Fremont 1427
CA Lake Arrowhead 254
CA Emeryville 1857
VA FALLS CHURCH 767
CA Mission Viejo 358
MD BALTIMORE 369
CA San Clemente 340
VA ARLINGTON 995
WA Kirkland 877
VA ARLINGTON 61
MD ASHTON 269
DC WASHINGTON 767
TX Georgetown 205
MD SILVER SPRING 443


What I'm trying to figure out is a formula to go through, and give me the averages of each State. I was thinking the AverageIf, but I'm not too familiar with it, and when i tried I got an error, saying it was trying to divide by Zero. Any help would be appreciated. :-(

[npatridge]

Put this in D2 and copy it down to get a unique list of states:

=IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$15),0,0),0)),"")

Then, in E2, put this to get the averages:

=IFERROR(AVERAGEIF($A$2:$A$15,D2,$C$2:$C$15),"")