Average number of days between orders of a customer

[taeyeon22]

Hi there! I have a table like below:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Order Date[/TD]
[TD]Brand[/TD]
[TD]Product ID[/TD]
[TD]Customer ID[/TD]
[TD]Supermarket[/TD]
[TD]Sales Volume[/TD]
[TD]Sales Value ($)[/TD]
[/TR]
[TR]
[TD]01/09/2016[/TD]
[TD]Company A[/TD]
[TD]3431[/TD]
[TD]345452[/TD]
[TD]Supermarket B[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]02/09/2016[/TD]
[TD]Company A[/TD]
[TD]3524[/TD]
[TD]243784[/TD]
[TD]Supermarket B[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]02/09/2016[/TD]
[TD]Company B[/TD]
[TD]3122[/TD]
[TD]345452[/TD]
[TD]Supermarket A[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


I'd like to answer:
Average number of days between orders of a customer

How do I first group orders by each unique customer, find the days between their orders, then find the average?

Thanks for the help guys and gals!

 

[barry houdini]

Won't the average number of days between orders for any one customer be the same as

=(MAX(Date)-MIN(Date))/(Count_of_dates-1)

so without sorting, assuming dates in column A, customer IDs in D then you can get that with this formula

=SUM(AGGREGATE({14,15},6,A$2:A$1000/(D$2:D$1000=F2),1)*{1,-1})/(COUNTIF(D$2:D$1000,F2)-1)

for a specific ID placed in cell F2

 

[taeyeon22]

Won't the average number of days between orders for any one customer be the same as

=(MAX(Date)-MIN(Date))/(Count_of_dates-1)

so without sorting, assuming dates in column A, customer IDs in D then you can get that with this formula

=SUM(AGGREGATE({14,15},6,A$2:A$1000/(D$2:D$1000=F2),1)*{1,-1})/(COUNTIF(D$2:D$1000,F2)-1)

for a specific ID placed in cell F2

Hi Barry,

Thanks for your answer. However, i don't quite understand the 'specific ID placed in cell F2' part. F2 is for the sales volume, but we're comparing it to column D for customer IDs?

 

[barry houdini]

Sorry, I wasn't paying attention to your table. F2 could really be any blank cell.....so lets say you get a list of all customer IDs (if you don't have one already you could use Advanced filter on column D and get a unique list) and put that list in a blank column, e.g. column J, so if your list of IDs starts at J2 you could adjust the formula I suggested to this in K2:

=SUM(AGGREGATE({14,15},6,A$2:A$1000/(D$2:D$1000=J2),1)*{1,-1})/(COUNTIF(D$2:D$1000,J2)-1)

copy down and that should give you the average number of days between orders for each customer. If any customer has only 1 order then the above formula will give #DIV/0! error because there is no valid average in that case. If you want you can use IFERROR function to return another value, e.g.

=IFERROR(SUM(AGGREGATE({14,15},6,A$2:A$1000/(D$2:D$1000=J2),1)*{1,-1})/(COUNTIF(D$2:D$1000,J2)-1),"One Order")​

​

 

[taeyeon22]

Sorry, I wasn't paying attention to your table. F2 could really be any blank cell.....so lets say you get a list of all customer IDs (if you don't have one already you could use Advanced filter on column D and get a unique list) and put that list in a blank column, e.g. column J, so if your list of IDs starts at J2 you could adjust the formula I suggested to this in K2:

=SUM(AGGREGATE({14,15},6,A$2:A$1000/(D$2:D$1000=J2),1)*{1,-1})/(COUNTIF(D$2:D$1000,J2)-1)

copy down and that should give you the average number of days between orders for each customer. If any customer has only 1 order then the above formula will give #DIV/0! error because there is no valid average in that case. If you want you can use IFERROR function to return another value, e.g.

=IFERROR(SUM(AGGREGATE({14,15},6,A$2:A$1000/(D$2:D$1000=J2),1)*{1,-1})/(COUNTIF(D$2:D$1000,J2)-1),"One Order")​

​

Thanks Barry, the formula worked perfectly! Just one extra question - so if i'd just want to calculate the average only for customers with more than 2 orders, how do i modify the formula? Thanks again, you've been a great help.

 

[barry houdini]

If you only want to average for customers with 3 or more orders then change to this version

=IF(COUNTIF(D$2:D$1000,J2)>=3,SUM(AGGREGATE({14,15},6,A$2:A$1000/(D$2:D$1000=J2),1)*{1,-1})/(COUNTIF(D$2:D$1000,J2)-1),"Fewer than 3 orders")

 

[soupp]

Sorry to necro this thread, but just wanted to understand more. In the formula

=SUM(AGGREGATE({14,15},6,A$2:A$1000/(D$2:D$1000=J2),1)*{1,-1})/(COUNTIF(D$2:D$1000,J2)-1)

What is the use of *{1,-1}?

 

[Fluff]

It multiplies the max date by 1 & the min date by -1