+ Reply to Thread
Results 1 to 2 of 2

License budgetting: Budgetting with continous payments using IF, MOD and starting date

Hybrid View

  1. #1
    Registered User
    Join Date
    09-25-2018
    Location
    Odense, Denmark
    MS-Off Ver
    2016
    Posts
    1

    Lightbulb License budgetting: Budgetting with continous payments using IF, MOD and starting date

    Hello!
    I would like to create a formula that evaluate the starting year of a contract, which inserts the value of the cost under the corresponding year in a budget. The catch is; i need to have intervals inserted also. For example: A contract from MS has to be paid every third year, the cost is 5000$ and the starting year is 2015. Therefore, in the budget, 5000$ should be inserted in the starting year 2015 then 2018, 2021 and 2024 etc.

    For now my formula says;

    =IF(STARTING YEAR=YEARINBUDGET;COST;"")

    I simply can't figure out how to then copy, the amount inserted, if the argument is true, to the remaining years when the license has to be paid. I have tried using MOD, but this does not seem to work.

    Picture to visualize what i imagine it will look like.

    til forum.PNG



    This should be build as a tool that can easily be updated continuously. :-)

    Thanks in advance

    //AV

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,889

    Re: License budgetting: Budgetting with continous payments using IF, MOD and starting date

    Hello avo95 and Welcome to Excel Forum.
    Using a modification of the setup pictured in post #1 the formula that displays the amount only in the appropriate years is:
    Formula: copy to clipboard
    =IF(AND(E$1>=$B2,MOD(E$1-$B2,$D2)=0),$C2,"")

    For future reference you usually get quicker and better responses if you upload a .xlsx file as opposed to a screen shot.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 3
    Last Post: 03-08-2018, 08:24 PM
  2. Calculating interest rate with changing payments and baloon payments
    By weirgr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2017, 04:01 PM
  3. Replies: 2
    Last Post: 12-30-2016, 03:54 PM
  4. [SOLVED] Loan Payment Schedule with Interest-Only Payments & P & I Payments
    By Masscatz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-20-2014, 11:51 AM
  5. vba print continous with paper continous and epson LQ300?
    By heroic in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-07-2013, 03:22 AM
  6. Replies: 2
    Last Post: 05-22-2013, 12:38 PM
  7. How do I forecast future payments by analyzing past payments?
    By CeeBee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2005, 03:06 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