Calculating which Cohort is driving revenue|General Excel Questions & Answers|Excel Forum|My Online Training Hub

I have a mdoel where a growth assumption has been attached to sales and a pattern of buyer behaviour assumed. For simplicity a linear pattern of behaviour of customers has been assumed (although in my main spreadsheet this can be amended by changing the assumption around how often a customer buys). Over a 36 month period there will be 36 cohorts of customers. In a customer's first year they will purchase in month 1, and 9 (and always buy 1 item). 

Therefore in month 5, Cohort 1 is joined by Cohort 5 (and in Month (, Cohort 9 is joined by Cohort 1 and Cohort 5 etc etc).

In the attached example in Month 1 there are 10 sales (therefore 10 customers). In month 5 there are 241 sales so there will be 10 customers from cohort 1 and 231 from cohort 5. I am trying to write a formula that will automate that calculation but struggling at the moment - any help much appreciated.

Thanks

Richard