How do I calculate averages based upon criteria that exist in other cells
excel
Solution
The formula for Year-average would be
=AVERAGE(IF(($A$2:$A$7=A2)*($B$2:$B$7=B2),$C$2:$C$7,""))
And for Site-average
=AVERAGE(IF($A$2:$A$7=A2,$D$2:$D$7,""))
Enter it as an array formula using Ctrl-Shift-Enter into the first cell and copy down the whole column. If you need to exclude duplicate years when counting the site average, the formula becomes a little longer:
=AVERAGE(IF(($A$2:$A$8=A2)*(FREQUENCY(($A$2:$A$7=A2)*($B$2:$B$7=B2),($A$2:$A$7=A2)*($B$2:$B$7=B2))>0),$D$2:$D$7,""))
Note there's an extra row in the first occurrence of the input range: $A$2:$A$8 rather than $A$2:$A$7.
Problem
Perhaps this question is best explained in tables, rather than in words. I'm trying to turn this table: ``` Site | Year | Value A | 1999 | 5 A | 1999 | 10 A | 2001 | 12 B | 1999 | 6 C | 1996 | 4 C | 1996 | 10 ``` into this table: ``` Site | Year | Value | Year-Average | Site-Average A | 1999 | 5 | 7.5 | 9.75 A | 1999 | 10 | 7.5 | 9.75 A | 2001 | 12 | 12 | 9.75 B | 1999 | 6 | 6 | 6 C | 1996 | 4 | 7 | 8.5 C | 1996 | 10 | 7 | 8.5 ``` If you'll notice, the Year-Average averages duplicate values within one year from within one site. Then the Site-Average calculates its averages from the Year-Average values. I can do this manually, but my real data set has many thousands of rows and I am looking for a repeatable procedure to use in the future.