Calculate the average by criteria (name and contract period)

Question

I have a dataset that resembles the following:

name  year contracted-period salary
peter 2012 2012-2014         $65
peter 2013 2012-2014         $70
peter 2014 2012-2014         $90
tom   2012 2012-2013         $110
tom   2013 2012-2013         $140

What i want is to create a new column that has the average salary for each name over that contract period i.e. each row in the column for peter would have his average salary over the contract

name  year contracted-period salary average-per-contract
peter 2012 2012-2014         $65    $75
peter 2013 2012-2014         $70    $75
peter 2014 2012-2014         $90    $75
tom   2012 2012-2013         $110   $125
tom   2013 2012-2013         $140   $125

I imagine the formula will have something to do with averageifs but i haven't been able to work it out.

Any help would be amazing

Answer 1

If your data table starts in A1, then the following in cell E2 will work (then fill down):

=AVERAGEIF($A$2:$A$6,A2,$D$2:$D$6)

(assumes you have data only for the contracted period, and 1 contract period per person)

If you have more than 1 contract period per person, add a helper column to make a unique ID in column E:

=A2&"_"&C2

Then run the sum equation on that ID instead of name:

=AVERAGEIF($E$2:$E$8,E2,$D$2:$D$8)