Results 1 to 3 of 3

Thread: Sumproduct, Mod question
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Sumproduct, Mod question

    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!

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Sumproduct, Mod question

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

  3. #3
    New Member
    Join Date
    Feb 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumproduct, Mod question

    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •