Formula or Pivot? Advice welcomed on how to achieve customer lifetime statistics

[Bergentrude1]

Hi All,

I have a spreadsheet with approx 15,000 individual invoice transactions, each row with a date, value and customer name.

Using a pivot, I can easily work out a total lifetime value of each unique customer along with how many transactions that customer placed, but what I cannot figure out is how to calculate how long that customer has been with us?

I am hoping, given that each transaction has a date, that I can somehow work out the length of time between the earliest date a customer has ordered, to the last date a customer has ordered.

I just have no idea how!

I attach a very small sample of the data here and would be most appreciative for advice.
Many thanks

[Glenn Kennedy]

Not sure what output you want to see. But... Select column C.

data/Data Tools/Text to Columns/Finish

Then in M2, an array formula (in my version of Excel, anyway!!):

=MAX(IF($E$2:$E$14=E2,$C$2:$C$14)-MIN(IF($E$2:$E$14=E2,$C$2:$C$14)))

copied down.

[Glenn Kennedy]

Non-array version:

=AGGREGATE(14,6,$C$2:$C$14/($E$2:$E$14=E2),1)-AGGREGATE(15,6,$C$2:$C$14/($E$2:$E$14=E2),1)

[Bergentrude1]

Hi Guys,

That's great, works perfectly, thanks for your help.

[Glenn Kennedy]

Guys = Guy!!

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Bergentrude1]

Sorry Glenn! I saw 2 posts and didn't spot they were both from you. Much appreciated! :o)

[Glenn Kennedy]

LoL. I don't think I'm schizophrenic. Are we??

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.