How to calculate the time between purchases for multiple customers?

[my_sunshine]

Hi all,

I'm new here and I'm looking forward to learning from your Excel knowledge.

My customers vary a lot. Some came back and bought HW, SW or P often. Some only bought the products once over the last 8 years (since 2004).

I've been manually looking at individual customers (from the list of 400 customers) and use yearfrac to calculate the time interval between their purchases, and I found the formula very inefficient. For example, customer A purchased HW, SW or P in Jan-05, Nov-06, and Dec-06 respectively. My manual work shows it was 1.83 year between the 1st and 3rd orders, and 0.083 year between the 3rd and 4th orders. You don't see any data for the Feb-06 because A bought something else, rather than the HW, SW and P.

I'd like to set up a formula to identify those repeat purchases across customers (file Repeat customers.xlsx) and calculate the time intervals between the purchases (regardless HW, SW or P), and label those purchases (in the next columm) as 1st purchase, 2nd purchase, 3rd purchase etc.

Please help.

Thank you so much,

Angie

[ChemistB]

Not sure how you want this laid out but assuming what you gave is an accurate portrayal of your data,

In F2 (this column can be hidden if you want)
=IF(ISTEXT(A2),A2,F1)
In G2
=IF(ISTEXT(A2),"N/A",B2-B1)
In H2
=IF(G2="N/A", 1,H1+1)
Is this what you are looking for? (See attachment)

[my_sunshine]

Thank you so much, ChemistB. We almost there.
Is there a way to NOT calculate the time intervals for the rows that have no HW, SW nor P purchases e.g. row 3, 6, and 7? We can't delete those rows because the customers purchased other products rather than the HW, SW and P. Also I don't want to label/count those empty rows in the purchase # column i.e. H2 =1 and H4 = 2.
I guess the alternative is to delete the formulas in those rows but the results would be wrong.
Thanks a million.
P.S. can I star you now even I still have more questions? You deserve multiple stars.

[ChemistB]

Okay, getting a little more complex now.
In F2 copied down
=IF(AND(ISTEXT(A2),SUM(C2:E2)>0),A2,IF(SUM(C2:E2)>0,LOOKUP("ZZZ",$A$1:A1),0))
I formatted the cells in this column as custom ;;- to create dashes for zeros.

In G2 copied down
=IF(OR(COUNTIF($F$1:F1,F2)=0,ISNUMBER(F2)),"N/A",INDEX(B:B,ROW())-INDEX(B:B, MATCH(REPT(F2,2),$F$1:F1,1)))
This puts in N/A if there is no previous values for this customer or if there's a zero in F2, otherwise subtracts the date from this row (col B) minus date in last row that had this customer in column F.

In H2 copied down
=IF(ISNUMBER(F2),"",COUNTIF($F$2:F2,F2))
If there's a zero in F2 then blank, otherwise count how many times customer appeared in Column F.
See attachment
Questions?

PS. You can star whenever you like but the system will not let you star a single individual more than once in a row, I believe.

[my_sunshine]

Thank you so much. You're my lifesaver. Thank you, thank you, thank you....

[ChemistB]

Glad to help and thanks for the rep.