Calculating average life of customer

DEW67

New Member
Joined
Apr 20, 2018
Messages
7
Hi Everyone,

It's my first time posting so please bear with me. I have an excel file with 70K lines of data. The first column is the client name and then the subsequent columns are the years from 2009 to 2018. The values in the year columns represent the spend by each customer in that year. I want to calculate how long a customer has been with the company based on the first time they purchased to the last time they purchased from us. If there are purchases made every year then I can calculate how long the customer has been active. If the customer has a year where they didn't buy something, then I don't know how to add in the year that didn't have any spend.

For example, if a new client started in 2009 and bought something from the company every year until 2013, I would say the client has been active for 5 years. How can I automate the calculation where the client started in 2009 and they bought something every year until 2013 with the exception of 2012. I want the result to still be 5 years.

I hope I'm explaining this correctly. Any help is appreciated.

Thank you
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Welcome to the forum.

Just to clarify: if a customer had an expenditure in 2009 and then he had an expenditure in 2012, you really don't care about the years between, right? In other words you just want to calculate his earliest expenditure date and his latest.

Also, please provide some sample data together with expected outcome so we can conduct experiments.
 
Upvote 0
ABCDEFGHIJK
1Count2009201020112012201320142015201620172018
2101267895751
3414

<colgroup><col width="64" style="width:48pt" span="12"> </colgroup><tbody>
</tbody>


I used this formula in A2:

Code:
=IFNA((INDEX(B$1:K$1,MATCH(9.99999E+307,B2:K2))-INDEX(B$1:K$1,MATCH(TRUE,INDEX((B2:K2<>0),0),0)))+1,0)
It works IF the "skipped" years are blank, but not if they are "0".

<colgroup><col width="64" style="width:48pt" span="11"> </colgroup><tbody>
</tbody>
 
Upvote 0
Hello

Code:
Start in M2 = aggregate(15,6,($B$1:$K$1)/(B2:K2>0),1)
End   in N2 = aggregate(14,6,($B$1:$K$1)/(B2:K2>0),1)

regards
(may be some spelling/separate mistakes)
 
Upvote 0
Thank you all for posting. I tried hotabae's idea since it was listed first. It worked but I have no idea why. I thought I was good at excel until I posted this question and saw the solutions :)

I'll definitely be back with more excel questions. Wish I had found this site sooner.

Take care.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top