Excel: Average of parts of a column based on another column

Question

Using Excel 2011 for mac, I have three columns: ID, Start date, end date and time to completion. (Date format: dd/mm/yyyy)

ID  | Start      | End        | Time
1   | 01/01/2016 | 05/01/2016 | 4
2   | 04/01/2016 | 08/01/2016 | 4
3   | 01/02/2016 | 14/02/2016 | 13
4   | 02/02/2016 | 20/02/2016 | 18
5   | 01/03/2016 | 05/03/2016 | 4
6   | 06/03/2016 | 08/03/2016 | 2
7   | 12/03/2016 | 15/03/2016 | 3

Column D is basically the difference between column C and column B. Now I have the total average, which is easy to calculate but I'd also like to have the averages for the different months. And this is where my knowledge falls apart. I've tried several things but I can't seem to figure out how to calculate an average using only the cells in a column that match a certain value in another column. I could sort the tickets by date and do it manually by doing the average for only a certain range but as this list constantly changes this is definitely not a nice option.

Answer 1

Check out the AVERAGEIF function

AVERAGEIF(selection_range, criteria, averaging_range)

It uses the values in the selection_range to filter which values in the averaging_range will be averaged.

In your case you could say AVERAGEIF(B1:B8, "01/01/2016", D1:D8)

Answer 2

There are multiple ways. I would personally use an array formula, but that may be a bit advanced and overly complicated.

I suggest adding a column E "Month" - into E2 add:

=MONTH(B2)

Then copy cell E2 to E3:E8.

Now you can easily get a monthly average by applying the AVERAGEIF command:

=AVERAGEIF(E2:E8,2,D2:D8)

The second argument, 2, indicates February, but may be exchanged with any number from 1-12.