Formula to identify how long before cell value changes

megera716

Board Regular
Joined
Jan 3, 2013
Messages
144
Office Version
  1. 365
Platform
  1. Windows
I feel like that's a weird subject but here's what I'm trying to do. I've analyzed year over year sales by customer and identified when a customer was new, if they were an existing customer but their total sales increased, decreased or stayed flat YoY, or if they were lost. (None would indicate if they weren't a customer at all that year and also didn't have any prior year sales against which to compare).

What I would like to do is see, if a customer is "existing account decrease" in a given year, how many years after that before they are a lost customer (if ever). My data is from 2014-2019 YTD so for example on the first row where Loss is highlighted, we would say they were a loss after 1 year (because they were new in 2014, decreased in 2015 and lost 1 year after that in 2016).

I would also like to separately identify how many years do we have a customer before they are lost (as in the second highlighted "Loss"), even if they never have a decrease in sales YoY.


69626180_10156950491831725_4141168562989432832_o.jpg
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,223,157
Messages
6,170,418
Members
452,325
Latest member
BlahQz

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