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
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