Ask Your Question
0

average number of days

asked 2020-10-06 04:58:38 +0200

cv105 gravatar image

updated 2020-10-06 04:59:13 +0200

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 flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
1

answered 2020-10-06 11:24:37 +0200

tzot gravatar image

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)
edit flag offensive delete link more
0

answered 2020-10-06 06:41:12 +0200

Zizi64 gravatar image

Pleasr upload your sample file here.

I suppose it, that you need make a division with the results of these functions: =SUMIF()/COUNTIF() .

edit flag offensive delete link more
0

answered 2020-10-06 12:26:56 +0200

Opaque gravatar image

updated 2020-10-06 13:04:43 +0200

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)

edit flag offensive delete link more
1

answered 2020-10-06 18:09:36 +0200

mgl gravatar image

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

edit flag offensive delete link more

Comments

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

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

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

mgl gravatar imagemgl ( 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.

tzot gravatar imagetzot ( 2020-10-07 10:31:09 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-10-06 04:58:38 +0200

Seen: 89 times

Last updated: Oct 06 '20