Take the 2-minute tour ×
Mathematics Stack Exchange is a question and answer site for people studying math at any level and professionals in related fields. It's 100% free, no registration required.

I've got a spreadsheet, where I'm trying to calculate the amount of retained users over time for a subscription based service. https://docs.google.com/spreadsheet/ccc?key=0ArE8l42n3soadGJGRmtZMWdpeFZKSTQycEtKRXE5ZFE#gid=0

I can get the totals but it's currently a manual process and each new week requires more manual input.

Trying to see if there is a mathematical way in Excel to figure out line B23 without having to manually build out B18 to B22. I showed this to a few other people and they said I'd have to write a small program to get the answer, but it seems like it should be possible in Excel.

share|improve this question
    
I don't understand what you would put in B18 through B22. Are you really talking of filling in G18 through G22 by using the decay rate in B13? That seems to be what you are doing. –  Ross Millikan Feb 22 '13 at 18:34
    
I'm trying to get rid of line B18 to B22. The reason they are in there now is to calculate line 23. I'm basically trying to come up with a solution to find line 23, so that series can keep growing. –  Chain Feb 22 '13 at 19:10

1 Answer 1

It would make it easier if you would describe the calculation you are doing. It appears that from each month you retain 3% of the users the next month, then that figure is decreased by 10% in each succeeding month. So in line 23 you want the sum of 3% of last month's users + 3*0.9% of the month before + 3*0.9^2% of the month before that, all the way back to start. On that assumption, you can put in G23 =0.3*sumproduct(\$b17:f17,0.9^(f5-f5:\$b5)). the dollar signs anchor the start of the computation and you should be able to copy right for as many months as you want.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.