Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 07 Oct 2020 10:31:09 +0200average number of dayshttps://ask.libreoffice.org/en/question/269750/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.Tue, 06 Oct 2020 04:58:38 +0200https://ask.libreoffice.org/en/question/269750/average-number-of-days/Answer by tzot for <p>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.</p>
https://ask.libreoffice.org/en/question/269750/average-number-of-days/?answer=269785#post-id-269785Isn'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)Tue, 06 Oct 2020 11:24:37 +0200https://ask.libreoffice.org/en/question/269750/average-number-of-days/?answer=269785#post-id-269785Answer by mgl for <p>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.</p>
https://ask.libreoffice.org/en/question/269750/average-number-of-days/?answer=269844#post-id-269844Hello @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, MichelTue, 06 Oct 2020 18:09:36 +0200https://ask.libreoffice.org/en/question/269750/average-number-of-days/?answer=269844#post-id-269844Comment by tzot for <p>Hello <a href="/en/users/25709/cv105/">@cv105</a></p>
<p>Along with <a href="/en/users/89488/tzot/">@tzot</a>, 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.</p>
<p>Let's take 2 days : D1 and D2, the average number of day between them is <code>m2 = |D1-D2|/1</code> as there is only one interval. So <code>m1 = max(days) - min(days)</code></p>
<p>With 3 days, D1, D2 and D3, there are 3 intervals and the average number of days between them is
<code>m3 = (|D1-D2|+|D2-D3|+|D1-D3|)/3</code> but <code>|D1-D2|+|D2-D3|=|D1-D3|</code> => <code>m3 = 2*|D1-D3|/3</code>, 2/3 of the max distance between the days. With the example taken by tzot, the average would be 20 days.</p>
<p>With 4 days, it starts to be more complicated with 6 intervals :</p>
<p><code>m4 = (|D1-D2|+|D2-D3|+|D3-D4|+|D1-D3|+|D2-D4|+|D1-D4|)/6</code></p>
<p>Considering that <code>|D1-D2|+|D2-D3|+|D3-D4| = |D1-D4|</code> and <code>|D2-D4| = |D2-D3|+|D3-D4|</code>, then </p>
<p><code>m4 = (3*(|D1-D4|+|D2-D3|)/6</code>, which is <code>m4 = |D1-D4|/2 + |D2-D3|/6</code> so half of the max distance + a little bit of something</p>
<p>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.</p>
<p>Kind regards, Michel</p>
https://ask.libreoffice.org/en/question/269750/average-number-of-days/?comment=269945#post-id-269945Yes, my answer is very simplistic as @mgl stated; it all depends on what the OP really wanted, and mgl rightly asks for clarifications.Wed, 07 Oct 2020 10:31:09 +0200https://ask.libreoffice.org/en/question/269750/average-number-of-days/?comment=269945#post-id-269945Comment by mgl for <p>Hello <a href="/en/users/25709/cv105/">@cv105</a></p>
<p>Along with <a href="/en/users/89488/tzot/">@tzot</a>, 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.</p>
<p>Let's take 2 days : D1 and D2, the average number of day between them is <code>m2 = |D1-D2|/1</code> as there is only one interval. So <code>m1 = max(days) - min(days)</code></p>
<p>With 3 days, D1, D2 and D3, there are 3 intervals and the average number of days between them is
<code>m3 = (|D1-D2|+|D2-D3|+|D1-D3|)/3</code> but <code>|D1-D2|+|D2-D3|=|D1-D3|</code> => <code>m3 = 2*|D1-D3|/3</code>, 2/3 of the max distance between the days. With the example taken by tzot, the average would be 20 days.</p>
<p>With 4 days, it starts to be more complicated with 6 intervals :</p>
<p><code>m4 = (|D1-D2|+|D2-D3|+|D3-D4|+|D1-D3|+|D2-D4|+|D1-D4|)/6</code></p>
<p>Considering that <code>|D1-D2|+|D2-D3|+|D3-D4| = |D1-D4|</code> and <code>|D2-D4| = |D2-D3|+|D3-D4|</code>, then </p>
<p><code>m4 = (3*(|D1-D4|+|D2-D3|)/6</code>, which is <code>m4 = |D1-D4|/2 + |D2-D3|/6</code> so half of the max distance + a little bit of something</p>
<p>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.</p>
<p>Kind regards, Michel</p>
https://ask.libreoffice.org/en/question/269750/average-number-of-days/?comment=269852#post-id-269852Which is insufficient, as shown by the three examples given.Tue, 06 Oct 2020 18:48:47 +0200https://ask.libreoffice.org/en/question/269750/average-number-of-days/?comment=269852#post-id-269852Comment by Opaque for <p>Hello <a href="/en/users/25709/cv105/">@cv105</a></p>
<p>Along with <a href="/en/users/89488/tzot/">@tzot</a>, 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.</p>
<p>Let's take 2 days : D1 and D2, the average number of day between them is <code>m2 = |D1-D2|/1</code> as there is only one interval. So <code>m1 = max(days) - min(days)</code></p>
<p>With 3 days, D1, D2 and D3, there are 3 intervals and the average number of days between them is
<code>m3 = (|D1-D2|+|D2-D3|+|D1-D3|)/3</code> but <code>|D1-D2|+|D2-D3|=|D1-D3|</code> => <code>m3 = 2*|D1-D3|/3</code>, 2/3 of the max distance between the days. With the example taken by tzot, the average would be 20 days.</p>
<p>With 4 days, it starts to be more complicated with 6 intervals :</p>
<p><code>m4 = (|D1-D2|+|D2-D3|+|D3-D4|+|D1-D3|+|D2-D4|+|D1-D4|)/6</code></p>
<p>Considering that <code>|D1-D2|+|D2-D3|+|D3-D4| = |D1-D4|</code> and <code>|D2-D4| = |D2-D3|+|D3-D4|</code>, then </p>
<p><code>m4 = (3*(|D1-D4|+|D2-D3|)/6</code>, which is <code>m4 = |D1-D4|/2 + |D2-D3|/6</code> so half of the max distance + a little bit of something</p>
<p>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.</p>
<p>Kind regards, Michel</p>
https://ask.libreoffice.org/en/question/269750/average-number-of-days/?comment=269851#post-id-269851`.../(COUNT(A1:A3)-1)` in @tzot's solution takes care of the number of intervals.Tue, 06 Oct 2020 18:45:04 +0200https://ask.libreoffice.org/en/question/269750/average-number-of-days/?comment=269851#post-id-269851Answer by Opaque for <p>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.</p>
https://ask.libreoffice.org/en/question/269750/average-number-of-days/?answer=269801#post-id-269801Hello
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)Tue, 06 Oct 2020 12:26:56 +0200https://ask.libreoffice.org/en/question/269750/average-number-of-days/?answer=269801#post-id-269801Answer by Zizi64 for <p>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.</p>
https://ask.libreoffice.org/en/question/269750/average-number-of-days/?answer=269756#post-id-269756Pleasr upload your sample file here.
I suppose it, that you need make a division with the results of these functions: =SUMIF()/COUNTIF() .Tue, 06 Oct 2020 06:41:12 +0200https://ask.libreoffice.org/en/question/269750/average-number-of-days/?answer=269756#post-id-269756