Sumproduct, Mod question

[snoworb]

I really need help on this. So the idea is that I'm running a calculation of top-ups to an eWallet, so I have

a. Average top up amount ($47.50, b7)
b. Average top up frequency (once every X months, b8)
c. Number of new users (varies each month, row19)
d. Attrition rate (varies each month)
e. Month of running operations (starts with zero, row13)

So I have new users that come in and top up, and old users that might leave, or top up when the two months are over.

So to calculate my new money in, I'm using

=G19*$B$7+IF(G13>=$B$8,$B$7*SUMPRODUCT(--(MOD(COLUMN($B$19:F19)-2,$B$8)=MOD(G13,$B$8)),$B$19:F19,$C$21:G21),0)

My logic was that okay, so new users top up + old users is it now the alternate month for you to top up? If yes, then new users from alternate month multiplied by the cumulative attrition (even on months when you don't have to top up)

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 two days and it refuses to tally with my manual calculation!

 

[AlanY]

it would be easier if you can provide a sample data and expected result

 

[snoworb]

Apologies for the late return.

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!

Do advise if my steps are wrong, or if there's a better formula!