Calculating the average difference in days within a period

0
votes

I am trying to calculate the average difference between two columns in excel. The columns contain a planned and an actual date, I would like to get the average difference but only for planned dates within the last three months.

Example:

Planned Start Date | Actual Start Date

21/09/2013 | 25/09/2013
10/07/2014 | 16/07/2014
01/06/2014 | 30/06/2014

The formula should only take line 2 & 3 (line 1 is older than 3 months), look at the difference in days for each applicable line (line 2: 6 days, line 3: 29 days) and then show the average ( 17.5 days) of all applicable lines.

Does anybody have a formula for this? Excel really isn't my strong suit...

1
excel-formula
Give the example of data and what values do you expect to get. This will make it much easier for us to give you the solution. – ttaaoossuuuu
Just added an example, hope that makes it a bit more clear @taosique – Thomas Kuhlmann

1 Answers

2
votes

Assume your data is in A2:B4 then try this array formula

=AVERAGE(IF(TODAY()-A2:A4<=90,B2:B4-A2:A4))
  • It assumes each month is 30 days (hence the 90)
  • Press CTRL + SHIFT + ENTER to enter the formula as an array formula

Based on your example I get a result of 17.5 days.