Search code examples
excelpivotformulaaverageexcel-2003

Count Average Value per Any Arbitrary Grouping

I have a 2-column in Excel which pairs Age with a certain value:e.g.,

34 107.8
22 5
22 97
45 76.5
46 16

I need to create a table/chart/pivot which shows me the AVERAGE value per any arbitrary group. Let's say:

BRACKET   VALUE AVG
0-39      ...
40        ...
41        ...
42-60     ...
Solution

  • Say you have the age stored in in column A and your value in column B (I assume, you place the in rows 2-19, adjust accordingly)

    Store the minimum age of each bucket (0, 40, 41, 42, 60) in column D and add a really high value below the last value (e.g. 150).

    Excel 2003 solution: Enter this formula in E2 and then copy it down:

    =IF(SUMPRODUCT(($A2:$A19>=D2)*(A2:A19<D3)),SUMPRODUCT($B2:$B19*($A2:$A19>=D2)*(A2:A19<D3))/SUMPRODUCT(($A2:$A19>=D2)*(A2:A19<D3)),"No values")

    Excel 2007+ solution: Enter this formula in E2 and copy it down:

    =IFERROR(AVERAGEIFS($B:$B,$A:$A,">="&D2,$A:$A,"<"&D3),"No values")