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]

Here is a link that tells me I can't have a negative height / width, although in reality that's what I need. Is there anyone who can help find a way to trick excel here? Redirecting

 

[Avit]

And here is a screenshot: I am trying to figure out how to offset that last row, to bo backwards vs. the "Remaining" row, instead of forward

 

[bebo021999]

attach mini sheet or sharing file via gg drive, dropbox...

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Peter_SSs]

It might also help if you updated your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Avit]

attach mini sheet or sharing file via gg drive, dropbox...

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Thanks,

Inverse offset.xlsx
	K
34
Sheet1

I hope this worked as an excel attachment, otherwise, I also have a google sheet version: Offset Inverse Problem

Thanks a lot!!

 

[Avit]

Inverse offset.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2		Assumptions
3
4		Initial Price	25
5		COGS	20
6		Delivery	5							\
7		CAC	Need to Calculate
8		Subscrition value (Month)	12		Montly Inflation	0.25%
9		Average LifeTime (Months)	12		STDEV	12
10		# Products purchased every year	2.5
11		Marketing Expense	Equals the consumer price of the first 2 purchases, exc. subscription
12		# of buyers - first month	500
13		% of buyers getting subscription	50.0%
14		Growth (Monthly)	7.5%
15
16
17		Normal Dist. of LifeTime	1	2	3	4	5	6	7	8	9	10	11	12
18			0.021841	0.023493	0.025095	0.026621	0.028044	0.029339	0.030481	0.031449	0.032222	0.032787	0.033130	0.033245
19		Cumulatve	0.021841	0.045333	0.070428	0.097049	0.125093	0.154431	0.184912	0.216361	0.248583	0.281370	0.314500	0.347745
20		Remaining	0.978159	0.954667	0.929572	0.902951	0.874907	0.845569	0.815088	0.783639	0.751417	0.718630	0.685500	0.652255
21
22		Model
23			1	2	3	4	5	6	7	8	9	10	11	12
25		Subscribers	250	269	289	311	334	359	386	415	446	480	516	554
26		Cumulative Users ever subscribed	250	519	808	1119	1453	1812	2198	2613	3059	3539	4055	4609
27		Offset ???		244.5398468	501.3451974	769.9914895	1050.809337	1343.0284	1646.587527	1961.211329	2286.4215	2621.553313	2966.495704	3320.213716
28
29			I want to sumproduct and offset vs. the "ramaining" row, except, instead of the width going forward, I want it to go backwards
30			The formula I have in there (d27) is pretty much the formula I need , except it's going forward, when I want it to go backwards
31
32
Sheet1

Cell Formulas
Range	Formula
C18:N18	C18	=NORM.DIST(C17,$C$9,$F$9,FALSE)
C19,C26	C19	=C18
D19:N19	D19	=SUM($C$18:D18)
C20:N20	C20	=1-C19
C25:N25	D25	=ROUND($C$13*D24,0)
D26:N26	D26	=SUM($C$25:D25)
D27:N27	D27	=SUMPRODUCT($C25:C25,OFFSET($C$16,4,0,1,C17))

 

[Avit]

attach mini sheet or sharing file via gg drive, dropbox...

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Hi Again, did you have a chance to look at the problem? Thanks!!

 

[bebo021999]

Hi Again, did you have a chance to look at the problem? Thanks!!

Please update your account detail, Exscel version, as per request in #5 from Peter.

 

[bebo021999]

I don't understand how the OFFSET to go backward?.
For example, at E27:
=SUMPRODUCT($C25:D25, OFFSET($C$16,4,0,1,D17))
=SUMPRODUCT({250,269},{0.978159,0.954667})
= 2500.978159 + 2690.954667
= 501.3
If I understand correctly, you want the cells in row 25 to go forward, while the cell in row 17 goes backward:
=SUMPRODUCT({250,269},{0.954667, 0978159})
=SUMPRODUCT(250*0.954667 + 269*0.978159)

= 501.79
Is that correct?
If yes, in D27, copy accross:

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

If not, please manually enter a few result cells and explain the calculation to arrive at those results.