# Sumproduct, Mod question

#### snoworb

##### New Member
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!

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### AlanY

##### Well-known Member
it would be easier if you can provide a sample data and expected result

#### snoworb

##### New Member
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!

Replies
4
Views
220
Replies
3
Views
1K
Replies
3
Views
74
Replies
3
Views
757
Replies
11
Views
599

1,195,672
Messages
6,011,084
Members
441,581
Latest member
rp4717

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back