I have a row containing 3 or more dates in chronological order and I want to calculate the average number of days between those dates.
Isn’t the average number of days the maximum date minus the minimum date divided by the count of dates reduced by one?
For example, if I have three dates (Oct 01, Oct 04 and Oct 30 2020) in A1:A3, the following formula results to 14.5 :
=(MAX(A1:A3)-MIN(A1:A3))/(COUNT(A1:A3)-1)Hello @cv105
Along with @tzot, I would like to pose the right question : what is the average number of days between those dates but I will not provide the same answer. As the question is not closed, I suppose none of the proposed answers has been accepted and therefore I fill free to add up a little bit of complexity.
Let’s take 2 days : D1 and D2, the average number of day between them is m2 = |D1-D2|/1 as there is only one interval. So m1 = max(days) - min(days)
With 3 days, D1, D2 and D3, there are 3 intervals and the average number of days between them is
m3 = (|D1-D2|+|D2-D3|+|D1-D3|)/3 but |D1-D2|+|D2-D3|=|D1-D3| => m3 = 2*|D1-D3|/3, 2/3 of the max distance between the days. With the example taken by tzot, the average would be 20 days.
With 4 days, it starts to be more complicated with 6 intervals :
m4 = (|D1-D2|+|D2-D3|+|D3-D4|+|D1-D3|+|D2-D4|+|D1-D4|)/6
Considering that |D1-D2|+|D2-D3|+|D3-D4| = |D1-D4| and |D2-D4| = |D2-D3|+|D3-D4|, then
m4 = (3*(|D1-D4|+|D2-D3|)/6, which is m4 = |D1-D4|/2 + |D2-D3|/6 so half of the max distance + a little bit of something
I will not try to detail the 5-days average calculus, as I don’t want all of you to fall asleep meanwhile but I would suggest to really pay attention to the exact meaning of this average and maybe forget about this notion and replace it by another one.
Kind regards, Michel
Which is insufficient, as shown by the three examples given.
Yes, my answer is very simplistic as @mgl stated; it all depends on what the OP really wanted, and mgl rightly asks for clarifications.
Pleasr upload your sample file here.
I suppose it, that you need make a division with the results of these functions: =SUMIF()/COUNTIF() .
Hello
another solution calculating from the array of individual intervals:
=SUMPRODUCT(AVERAGE(A2:A3-A1:A2))
(though @tzot’s solution is the easiest and better fault tolerant solution, since it also works on an unsorted list of dates)