Finding difference in dates for repeat entries

**strikinglyfamiliar** · 09-27-2012, 02:17 PM

I have a file that I have pulled, using conditional formatting that shows repeat customers, and the dates they placed their orders. I want to show my boss a: how many times each customer has ordered, and the frequency of those orders using the data. Order Sample For Forum.xlsx

**Ace_XL** · 09-27-2012, 02:23 PM

=COUNTIF(C:C,C2) dragged down will give you number of times a customer has ordered.

You can also use pivit tables to achieve that
www.cpearson.com/excel/pivots.htm

What do you mean by frequency of orders?

**strikinglyfamiliar** · 09-27-2012, 02:26 PM

by frequency, I mean, how much time in between orders... once a month, twice a month, twice annually, or annually. I will try the countif formula, which is what I was thinking to begin with, but that date/frequency part is puzzling. THANKS

**strikinglyfamiliar** · 09-27-2012, 02:30 PM

also found that I needed to tweek the formula just a bit. " COUNTIF($C:$C,C2) " so that it changed per row. But works for that purpose just fine. Thanks again

**Ace_XL** · 09-27-2012, 02:34 PM

One way of approcahing frequency..

- Find earliest date of order for each customer
- Find latest date of order for each customer

(Diff between dates) divided by (number of orders less one) is frequency of ordering. Hence use
In row 2
=IFERROR((MAX(IF(C:C=C2,B:B))-MIN(IF(C:C=C2,B:B)))/(COUNTIF(C:C,C2)-1),"")

confirm with Ctrl+Shift+Enter and not just Enter

If a customer has ordered only once, there will be no frequency calculated, if twice its simply the difference between dates of orders, if more than twice its difference between dates divided by no. of orders less one

Does this work for you?

**strikinglyfamiliar** · 09-27-2012, 02:34 PM

Problem is "Customer A, has ordered 5 times in the last six months, customer B has ordered 3 times in the last six months, You are given the dates of the orders, but how often has each customer ordered... is it once a month, or what. I can already do a formula that would show if it's been a month, or two since their last order, but to show the boss how often they order (and to make this project easier in the future) I need a frequency calculation.

**strikinglyfamiliar** · 09-27-2012, 02:35 PM

AWESOME. I'll give it a try

**strikinglyfamiliar** · 09-27-2012, 03:15 PM

Well that just confused me. With the date data, in B2 (5/1/2012) and B3 (4/18/2012) according to the formula =IFERROR((MAX(IF(C:C=C2,B:B))-MIN(IF(C:C=C2,B:B)))/(COUNTIF(C:C,C2)-1),"") the result is 179... there's no way 179 days passed in a month

**Ace_XL** · 09-27-2012, 03:21 PM

Ensure that you confirm the formula with Ctrl+Shift+Enter as its an array formula. This will give you 13 as a result.

www.cpearson.com/excel/array.htm