2

I have an ever increasing list of dates (YYYY-MM-DD format from 8601:2004) running in a column from cell B5 down.

I want to add the average amount of days between all dates in A2.

example:

[B6] 2016-06-27

[B7] 2016-07-05

[B8] 2016-07-11

[B9] ...

then

[A2] AVERAGE=(B7-B6, B8-B7, B9-B8... to infinity).

How would I go about building a formula that automatically adds all new dates inserted in B column?

The only workaround I can think of is to add a separate column and add the difference between last date and second to last date in days right next to every new (and respectively last) date, but if I can avoid complicating the spreadsheet even more, I would love that.

2

If they dates are ordered, I.e. from the smallest to the biggest you could simply take the smallest and biggest divided by the number of dates -1. Basically

=(MAX(B6:B999)-MIN(B6:B999))/(COUNTA(B6:B999)-1)

In that case 999 would be the infinity.

  • 1
    Google Sheets allow the use of open references, in this case B6:B – Rubén Sep 10 '16 at 5:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.