Extracting the average time to complete each task over a specified date range

Hi everyone,

I have a situation in which there are –
• a series of different tasks in column A
• dates on which those taskss were completed in column B – as this spreadsheet would be kept over the course of the year, that date could be in any month.
• the length of time it took for that task to occur – which could range from 2 or 3 days up.

I'd like to be able to examine how we performed, on average, for each of the activities during a stated date range which will usually be a month, but, I guess could be a different period.

For example, in January, I'd like to show the average of days for simple activities completed in the month and the average of days for complex activities completed. It's no problem if the time taken was spread over a couple of months – I'm just looking at the average for each completed activity.

I've attached a sample spreadsheet.Sample.xlsx

Any ideas?

I prefer not to use VBA programming as it's a work application and they get a little dickie about that.

Thanks in advance,

Tim

Tim,

I think this is what you mean.

The start and end date can be amended to alter the date range.

Sorry, wrong example version attached originally.

This one uses AVERAGEIFS to do the same thing.

Apologies Tim, my fault entirely. In my test version I'd taken tht into account.

I'm replying from an ipad with no spreadsheet access so this formula may need a little tweaking to get the date cells right.

In the yellow cell to the right of 'simple task' enter the below and copy down.

=AVERAGEIFS(C:C,A:A,F6,B:B,">="&$G$3,B:B,"<="&$G$4)