# average number of days

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.

edit retag close merge delete

Sort by » oldest newest most voted

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)

more

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

more

.../(COUNT(A1:A3)-1) in @tzot's solution takes care of the number of intervals.

( 2020-10-06 18:45:04 +0200 )edit

Which is insufficient, as shown by the three examples given.

( 2020-10-06 18:48:47 +0200 )edit

Yes, my answer is very simplistic as @mgl stated; it all depends on what the OP really wanted, and mgl rightly asks for clarifications.

( 2020-10-07 10:31:09 +0200 )edit

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)

more