1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for Qtrly Payments

Discussion in 'Ask an Excel Question' started by slstewart249, May 15, 2018.

  1. slstewart249

    slstewart249 New Member

    Messages:
    14
    I am trying to write a formula to show quarterly payments for the next 5 years.

    The Start Date is Feb-19
    Qtrly Payement is $25,000

    I've tried various forms of IF(AND but its not working.

    HELP!
    FILE IS ATTACHED.

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,354
    D8: =IF(MOD(D5,3)=MOD(MONTH($A$7),3),$A$8,0)
    copy accross
  3. AlanSidman

    AlanSidman Active Member

    Messages:
    267
    Wouldn't =$A$8 be what you need. If this is not what you are looking for, then explain in more detail what you are looking for as it is not currently clear.
  4. slstewart249

    slstewart249 New Member

    Messages:
    14
    Thanks - One point I should have been more clear about is the payment table starts with 2018.

    How do I revise the above formula so result is 0 for all months in 2018, and then shows $25,000 starting in Feb 19? With the current formula it shows paymetn for 2018 too. Update file is attached.

    Attached Files:

  5. slstewart249

    slstewart249 New Member

    Messages:
    14
    Also, I have multiple contracts with different payment start dates. The MOD formula only seems to work if the payment date starts in the 2nd month (2 i.e Feb).
  6. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,666
    Try,

    In D8, copied across :

    =IF(0+TEXT($A$7,"mm/yyy")<=0+TEXT(DATE(D4,D5,1),"mm/yyy"),IF(MOD(D5,3)=MOD(MONTH($A$7),3),$A$8,0),0)

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  7. slstewart249

    slstewart249 New Member

    Messages:
    14
    Thank you! One more feature to the formula.

    If I drag the formula below across the rows, it shows payment each quarter every year. I have a multi-year table.

    How would I write it so payment occurs only in 2019?
    Thanks

    =IF(0+TEXT($A$7,"mm/yyy")<=0+TEXT(DATE(D4,D5,1),"mm/yyy"),IF(MOD(D5,3)=MOD(MONTH($A$7),3),$A$8,0),0)

    Attached Files:

  8. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,666
    Payment occurs only in 2019,

    In D8, revised formula (add highlighted red portion condition) copied across

    =IF((0+TEXT($A$7,"mm/yyy")<=0+TEXT(DATE(D4,D5,1),"mm/yyy"))*(0+YEAR($A$7)=D4),IF(MOD(D5,3)=MOD(MONTH($A$7),3),$A$8,0),0)

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page