# 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.

First time here? Check out the FAQ!

average number of days

add a comment

1

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)
```

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

0

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)

0

Pleasr upload your sample file here.

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

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

Seen: **88 times**

Last updated: **Oct 06 '20**

Auto Formatting Sheet based on date [closed]

What is the equivalent of the DATEINFO function in Lotus 123? [closed]

How do I export a chart in an image format from LibreOffice Calc? [closed]

Are there plans for a "papercut" project for libreoffice [closed]

Is it normal for Calc goal seek to take very long? [closed]

Please refine "Search" in Calc - implement functions in Gnumeric [closed]

LibreOffice Calc will not link to external data via internet [closed]

Is there a LibreOffice .odt, .ods viewer for Android? [closed]

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.