- assumptions section, where I'm asking some users to fill in their assumptions on how long a few sets of cohorts (based on their joining date, across the top of the range, which can be read as 'anyone between two dates gets the earlier date's numbers'...hence use of index-match(1) for the pivot area's lookup)
- Three cohorts (for now, A, B, C) and the number of months they are predicted to stay active
Top part of pivot data, assume I have lots of numbers that add up to a certain number of users, each with a joining date that's the cohort to which I'm referring above). I now need a section, withing which the following is calculated (for any given month of the model...so I'd repeat this 36 times, as this is a 3yr model):
- in a particular month, the lookback calculation that would give me the % number relevant to a cohort, based on the fact that if you took that cohort's date, and added the number of months from the reference table, it would put you at the current row's month
in other words, the reference table example is that 50% of the people joining in April '19 would be predicted to stay active for 6 months. If that's the case, then as long as I have a table (which I will) which gives me, by joining month, the number of people that join, then in Octo '19 I need to record a 50% drop of that number.
I have demonstrated how I can find that number 6 pretty easily. But that's because I'm looking up Apr 19. How do I tell the formula next to Oct 19, effectively, "hey, look at that table, and because the difference between you and Apr 19 is the 6 that's in the table under Apr '19, you need to take the 50% associated with the 6, and use that in this other calculation I need to run (the calculation being my grabbing the number of people that signed up in Apr 19, and applying a 50% drop factor for that number.
Hope it makes sense. If the logic is unclear, please don't give up on me and ask for anything I can clarify. I'm really stuck, and I really need this formula to work.
@Twifoo, thanks for responding. Not sure I can provide more than my Excel sheet. That actually is representative of the actual section of the Assumptions table (left part) and the Pivot data (right part). Perhaps if you could tell me which part of the explanation and sheet aren't clear, I'd be happy to state it in a different way. If that helps you understand the issue better (and thus help me with a solution) I'm all for it.
@Twifoo, I solved for at least part of the problem. Though now I've created another one. In attached (revised) version, what I've accomplished is I have set up two individual areas of calculation. The lines from one should equal the sum of the three. The 12 individual calcs at upper right have the advantage of iferror to set themselves to 0. This happens in the cases where the EDATE (which was the find I made that solves for how to backwards calculate my months easily) takes the feeder date back so far, that it predates my model options.
In the sumproduct array version of those calculations (which is what the 2.1 is incorrectly calculating), if you evaluate the formula you'll see that the way I've set it up, the IFERROR doesn't individually try to find the pivot items, and only in those cases give 0...instead it says "if any of these dates are not in the table, ignore the whole batch of results", as opposed to what I want it to say, which is "if any of these dates are not in the table, just ignore that date".
Is there a different way to protect for errors in this part of the formula. Again, I found the evaluate formula tool very helpful for isolating the problem. Just don't know how to restate the functions to fix it.
The second problem (which gets to another question I've posted) is that the reference range of assumptions right now shows 4 dates. Sometimes, I may have only 3 date ranges (sometimes 5, 6, etc.). Just with this example, if you delete the part of the assumptions that are on the right (column E section), you'll see the results. This is where I need to be able to tell the lookup formula "start with the B column for your ranges, but only go to the right the number of spaces that are filled contiguously". This is secondary, and I can probably live without it, but if I could have this as an embedded part of the formula, would be good.
Thanks for the continued effort. At least it's closer
What I want you to specify is the result you want. You have to manually enter that result based on your given data. Thereby, I will suggest a formula you can use so that you won’t have to manually enter the result you want.
@Twifoo, believe the last version I sent, with note, should clarify. I can do the calculations in steps now. Just need to get it more condensed. Hopefully last message and file make sense. If not, let's focus on where that doesn't make sense, and I'll clarify anything still unclear.