+ Reply to Thread
Results 1 to 3 of 3

How to allocate a certain amount over a 12 month time period through formulas in excel

  1. #1
    Registered User
    Join Date
    07-11-2020
    Location
    Copenhagen, Denmark
    MS-Off Ver
    2020
    Posts
    1

    Lightbulb How to allocate a certain amount over a 12 month time period through formulas in excel

    Hi guys,

    I am trying to understand how to overcome this problem that I have been struggling with.

    So there is a certain amount (say, $1000) post which I have an additional transaction charge (per $, say 50 cents). And say, every month I have a certain spend (which depends on formulas so, not fixed). I have a column for "additional charge" which essentially has to check for each month whether it crosses the 1000 threshold (annual) and then apply the per $ charge to the cell. Now I tried using an "if" command, which works. But the challenge is that, once in any particular month (or cumulatively till that month) the amount spent crosses $1000, I have to account for the per $ charge. But the very next month (assuming it is still in the same year) will not have any more bandwidth for the $1000 limit left. So the entire charge on that month will be above the $1000 threshold so will be charged on that per $ basis.

    So, for now, I have been adding these formulae manually once the $1000 limit crosses. But I would like for a formula/ something where it checks 2 things, whether the spend has cumulatively crossed $1000 and whether that $1000 deduction has already been taken into account in the previous month. Hope this makes sense and you guys can help me out with it!!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,386

    Re: How to allocate a certain amount over a 12 month time period through formulas in excel

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: How to allocate a certain amount over a 12 month time period through formulas in excel

    Note that you can easily check cumulative sum from all rows above current row using mixed addressing. Say your single amounts are in column C and you "are" in row 16 so want to look on sum from rows 1 to 15
    use
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    for that. If you copy this formula down, you will notice it changes to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and so on. So you can use it in your IF formula.
    Best Regards,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Amount of number extracted depending on time period
    By Allerdrengen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-20-2019, 11:27 AM
  2. [SOLVED] Allocate Amount for Month
    By Billy Spivy in forum Excel General
    Replies: 1
    Last Post: 01-24-2018, 03:55 PM
  3. Deduct amount based on sum of distinct values over a period of time...
    By Mesanic in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-10-2017, 12:11 PM
  4. Replies: 10
    Last Post: 01-09-2014, 07:04 PM
  5. [SOLVED] VBA Form - Allocate volume in a period to a new worksheet.
    By macenmin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-07-2013, 11:58 AM
  6. Replies: 1
    Last Post: 01-19-2012, 01:26 AM
  7. Macro to allocate amounts to different period
    By raj8000 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2009, 03:00 PM

Tags for this Thread

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