Average number of days between orders of a customer

[taeyeon22]

Hi there! I have a table like below:

Order Date	Brand	Product ID	Customer ID	Supermarket	Sales Volume	Sales Value ($)
01/09/2016	Company A	3431	345452	Supermarket B	5	10
02/09/2016	Company A	3524	243784	Supermarket B	2	4
02/09/2016	Company B	3122	345452	Supermarket A	1	2

<tbody>
</tbody>

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