How do I calculate average amount of days between multiple dates?

Question

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.

Answer 1

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.