Totaling Moving Values by Week

Hi Everyone,

I have an attached spreadsheet.

If you take a look a tab 2, sheet 1, I have a data setby cohort and their assumed retention curve.

Row 2 has the total number rof people in that group.

In the summary page, I'd like to roll up the number of users by Week.

So Week 1 would be 2458 users.

Week 2 would be (2458 * 29%) + 3979 and so on as a cohort matures. How can this be designed?

Write this formula on B2 of Summary sheet.

Please Login or Register  to view this content.

Now copy down and right.

Hi Sanram,

Am I doing something wrong or did I possibly explain my need wrong?

So I've reattached the spreadsheet with your formulas and the results I think it should be.

Thanks again for your help!

No. You are not doing anything wrong. I couldn't understand your formula before. Sorry.

How would I automate that logic. That it takes a step down with each curve as a week progresses. I believe using the offset function might be best, no? Feel free to add any helper rows too. I am having a hard time envisioning it.

May be someone good in creating array formulas can help you better on that matter. But I am not that good. But here is my solution in attachment. But you have to create lot of helper row for this. The number of helper row will be the number of Cohort in Sheet1. This is the best I can do with formulas.

But I think you should wait for the response from an expert.