Average number of days between multiple events in a time interval.

[flex33]

Appreciate you folks possibly helping on this formula.

I am looking for a single cell formula, it will calculate the average number of days between orders during a 30 day period. (The value of the order doesn't matter) For simplicity imagine that three orders come in during the 30 days, on Days 3, 21, 22.


Date range is 1 through 30

The first interval average is 21-3=18

and the second interval is 22-21=1

The average interval overall is (18+1)/2 = 9.5 days


It seems easy, the hard part for me is the 'end of the first interval is the beginning of the second interval'. Maybe there is some advanced array formula that already does this?

 

[KRice]

If you're determining the average interval like that, then you don't need anything more than some counts. This assumes you manually set the range to begin when the first event occurs (that could be done in the formula too if desired):

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1
2	Average interval	6.33
3
4
5	Day	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
6				100						100												100	22
Sheet1

Cell Formulas
Range	Formula
B2	B2	=(COLUMNS(D6:W6)-1)/(COUNT(D6:W6)-1)

 

[KRice]

The yellow cells illustrate my last comment...the formula determines the first and last entries in the 30-day block and computes the average interval, and the formula in B2 can be ignored:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1
2	Average interval	4.6
3	Average interval	4.6
4
5	Day	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
6			100	100						100												100	100			100
7
8	Longhand	2	3	1
9		3	9	6
10		9	21	12
11		21	22	1
12		22	25	3
13				4.6
Sheet1

Cell Formulas
Range	Formula
B2	B2	=(COLUMNS(C6:Z6)-1)/(COUNT(C6:Z6)-1)
B3	B3	=(AGGREGATE(14,6,(COLUMN(B5:AE5)-COLUMN(A5))/(B6:AE6<>""),1) - AGGREGATE(15,6,(COLUMN(B5:AE5)-COLUMN(A5))/(B6:AE6<>""),1))/(COUNT(B6:AE6)-1)
D8:D12	D8	=C8-B8
D13	D13	=AVERAGE(D8:D12)

 

[flex33]

OMG KRice. It's amazing how some brains work in such better ways for solutions - yours in this case. Thank you, this solution is perfect!

 

[KRice]

Thanks...I'm happy to help.