+ Reply to Thread
Results 1 to 7 of 7

Summarising variable weekly payments to months

  1. #1
    Registered User
    Join Date
    09-12-2019
    Location
    London
    MS-Off Ver
    2019
    Posts
    2

    Summarising variable weekly payments to months

    Hi guys,

    It's rare I come to an excel help forum as I try and work things out for myself but alas, I'm stumped on the best way to achieve this.

    I have varying weekly interest, say 100 in week 1, 200 in week 2, 300 in week 3 etc and I'm simply trying to calculate the exact interest in the month. As the some weekly interest relate over two months, I'll have to pro rate to get the exact figure.

    Is there an elegant solution to this - my thoughts are utilising MINs and MAXs here. Can't seem to attach at the moment, but an example of what I'm trying to get to is provided below.

    Thanks!

    Example.PNG
    Last edited by tanLN; 09-12-2019 at 06:07 AM.

  2. #2
    Registered User
    Join Date
    10-08-2018
    Location
    Brasil
    MS-Off Ver
    Excel 2016
    Posts
    95

    Re: Summarising variable weekly payments to months

    Hello,

    So you want to sum the values between G1 and G2? if so, put this in G3:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and don't forget to press ctrl+shift+enter
    John.

    "I excel at jumping to conclusions"

  3. #3
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    1,553

    Re: Summarising variable weekly payments to months

    That's not what he's trying to do as 27.04 is included into the calculation due to the fact that the next payment starts on 04.05. But what I don't understand is how he arived at 836 for the second calculation
    Click the * to say thanks.

  4. #4
    Registered User
    Join Date
    10-08-2018
    Location
    Brasil
    MS-Off Ver
    Excel 2016
    Posts
    95

    Re: Summarising variable weekly payments to months

    I don't think the 836 has anything to do with the calculation. But if he wants the whole month:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    *CTRL+SHIFT+ENTER

    Cheers!

  5. #5
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    1,553

    Re: Summarising variable weekly payments to months

    This will calculate the totals in month based on a helper column:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-12-2019
    Location
    London
    MS-Off Ver
    2019
    Posts
    2

    Re: Summarising variable weekly payments to months

    Thanks guys. PaulM100 is bang on here.

    Was hoping to avoid helper columns because I have a lot of these interest amounts from other parties and it's not just one! Use of arrays didn't come into my head.

    Many thanks!

  7. #7
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    1,553

    Re: Summarising variable weekly payments to months

    I am not sure if is possible without a helper column, at least not for my skills. cause you need to calculate based on days from weeks dates instead of using month

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1