Search code examples
excelms-office

Excel : calculating average within a group

enter image description here

I'm working with excel 2016. I have some unstructured data as in the screenshot. I would like to group the rows by zip code , then calculate the average of all the values in each zip code. I'm pretty new to excel. Is there a function or group of functions which can do this?

Solution

  • Given your table of data, to determine the zip avg price/acre you can do a weighted average.

    • for each zip, divide the total sales price by the total acreage

    I changed the data into a Table so as to be able to use structured references; but you can change that to regular addressing if you prefer.

    The formula for doing the weighted average:

     =SUMIF([ZIP],[@ZIP],[price])/SUMIF([ZIP],[@ZIP],[acres])

    enter image description here

    Note that if you calculated the average of the price/acre for each zip code, you would be computing the Avg Price/Acre/Sale, not the Avg Price/Acre. For zips with multiple sales, the difference can be substantial

    =AVERAGEIF([ZIP],[@ZIP],[Price/Acre])

    enter image description here

    If you just want to show the zip and the avg price/acre, you can use a Pivot Table:

    • Drag zip => rows area
    • Create a Calculated Field with the formula =Sum(price)/Sum(acres) and drag that to the Values area

    enter image description here

    And, of course, if you should want the avg price/acre/sale, drag the Price/Acre to the Values area, and choose to summarize by average.

    enter image description here