Negative (backward) count offset for a sumproduct

[Avit]

I am trying to determine churn of a given amount of people, based on a norm. dist.
I created the norm.dist

So, the idea for me would be to do something like sumproduct (number of people joining per month) (offset, a, b, c, 1) and then continue that 1 to all eternity (or 4 years or 60 months, whatever)

The problem is that what I really need is to get that (offset a,b,c,1) to be something like (offset a, b, c, -1) because what I really want is to count the norm.dist backward in order to sumproduct with the new people that are coming in the platform.

How do I do that??

Thanks!

 

[Avit]

Thanks! not sure what you did there, but while it's not correct, it's close.
So, I took the long route and built a cohort (in the second tab) here to calculate what I couldn't calculate with offset. See the cohort in the second tab and line 28 in the original tab
I am sorry this is Google Sheet, but it's harder for me to share two tabs on an excel, and I think the calculation should end up the same anyway.
So, these are the correct numbers, in a "dubmer" solution than an offset, but I am really wondering if you could get the same results with an offset.

Mr. Excel Offset

Model Assumptions For subscribers,For non subscribers Initial Price,25,65 COGS,20 Delivery,5,\ Subscrition value (Month),12,Montly Inflation,0.25%,Startup Costs as. % of Revenue Average LifeTime (Months),12,STDEV,12,1,2,3,4,5 # Products purchased every year ,2.5,35%,20%,10%,0,0 Marketing Expens...

docs.google.com

Google SheetThanks again!!

 

[Avit]

 

[bebo021999]

My formula in #10, with add D26

D28, drag accross

=D26+SUMPRODUCT(($C26:C26)*SUBTOTAL(9,OFFSET($B$20,,MAX($C$17:C$17)-$C$17:C$17+1,1)))

 

[Avit]

Hey, sorry for not answering earlier
I really appreciate your help but unfortunately the solution was not correct. I reverted to my longer manual one. Thanks!!

 

[bebo021999]

I really appreciate your help but unfortunately the solution was not correct. I reverted to my longer manual one. Thanks!!

Why not?
Row 29 is my formula, vs row 28 is your expected output.