Sumproduct, Mod, Offset, If??

[snoworb]

Okay, so the context is that I'm trying to model the running of an e-wallet, where users are able to top up.

So basic assumptions:
1. Average top up value ($): D
2. Average top up frequency (top up every X months): T

Figures input
a. Operational month, O : 0 1 2 3 4 5 ...
b. New users that sign up every month, Xx : X0 X1 X2 X3 X4 X5 ...
c. Attrition rate (%), Ax : A0 A1 A2 A3 A4 A5 ...
d. New top up ($), Nx :
for example for N5, N5= X5*D + old users that top up again after 2 months, 4 months, 6 months, etc
so to find old users that top up again after 2 months, 4 months, 6 months, etc I believe requires the following steps:
Step 1 : determine if current operational month is the month for old users to top up >> Mod(O0:O4,T)=Mod(5,T)
Step 2 : if it is the month to top up (Ox), find the sign up numbers (Xx) >> I have no idea how to do this I tried sumproduct for this, so sumproduct(mod(O0:O4,T)=Mod(5,T),X0:X4, ... )
Step 3: if it is the month to top up (Ox), find the attrition rate of Ax all the way to A4 (because the attrition rate has to be cumulative, like 2% of the base disappears every year) >> this is where I know that my sumproduct (sumproduct(mod(O0:O4,T)=Mod(5,T),X0:X4, A1:A5 [A1 because there's no attrition at 0 operation month and attrition has to be counted all the way to the present month of 5]) can't work because I need a product of ALL the attrition rates, not just for the months where Mod(O0:O4,T)=Mod(5,T)

I've tried offset() but offset can't work on a range of cells. I've also tried hlookup() but i don't think that it works, and I really have no idea what to do!

Hope that someone will be able to help me! Do return if you need more information from me. I've been working on this for four days and it refuses to tally with my manual calculation!

 

[Gerald Higgins]

HI. I've read and re-read this, and have no idea how to do what you are asking.
Please bear in mind that probably most of the people on this forum will also have no idea of the calculations you are trying to do.
It's probably not necessary to go into too much detail about the real world application of your application.
Just focus on what do you have now, and what exactly do you want to do with it ?
And let's take it a step at a time.

So for STEP 1, what data do you have that is relevant to that calculation ?
Post several different examples of what the data might look like (make up some typical data if you don't want to post real data).
Explain what the results should be, and why.