Forum Discussion
Formula help - how to build a formula for a cohort model
Hi all,
I could really do with some help. I am building a sales forecast cohort model which i want to take the previous months units and apply a retention % on.
I am currently using this formula which works...partly.
014 is the previous months sale
Logic A7 is the top of the list
A8-A19 is the list of 1-12 (and 13+) and 1 is taking us across to the right of that list to give us the relevant % i want to apply.
=ROUND(O14*OFFSET(Logic!$A$7,INDEX(Logic!$A$8:$A$19,INT(YEARFRAC($A14,P$1)*12)),1),0)
This formula is basically saying - work out how many months are between the two dates, then find the correlating % from the logic tab and times that by the sales from last month.
This works absolutely fine until we get to the months between the 2 dates being greater than 12. I want anything greater than 12 to just be 1% (or a specific value) and don't want to have to do an endless list of 13,14,15...so on. Anything over 12 would be the exact same %.
How can i edit this formula so that when the yearfrac formula returns >12 it will pick up the right cell and won't just give me a REF error?
Really appreciate any help you can provide!
Thanks!
1 Reply
I will assume that you can extend the rate table on the logic tab to include a 13th entry (for month differences of 13 or more). To use that last entry (as well as the first 12), your calculation formula must then reference $A$20 instead of $A$19. And… The crucial change is then to limit the second INDEX argument to 13, by wrapping the YEARFRAC * 12 calculation inside either an IF function or (as I did) a MIN function:
=ROUND(O14*OFFSET(Logic!$A$7,INDEX(Logic!$A$8:$A$20,MIN(INT(YEARFRAC($A14,P$1)*12),13)),1),0)I am attaching a bare-bones spreadsheet that you can experiment with. Cells B15 and B16 on the Calcs worksheet are not needed, but help to see what is going on, the latter showing the "adjusted months".
