Sumproduct, Mod, Offset, If??

snoworb

New Member
Joined
Feb 21, 2019
Messages
3
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! :eek:

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!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top