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 Tiered payments - that result in fixed sum

Discussion in 'Ask an Excel Question' started by slstewart249, Jun 13, 2018 at 2:38 PM.

  1. slstewart249

    slstewart249 New Member

    Messages:
    19
    I am trying to write a formula for a tiered payment table. The sum of all payments must equal $75,000 and be spread across various tiers, with each payment being higher than than next. I am stumped.
    File is attached

    Thanks.

    Attached Files:

  2. GraH - Guido

    GraH - Guido Active Member

    Messages:
    663
    Perhaps, in C5 =$C$26/SUM($B$5:$B$24)*B5 and drag down.

    Attached Files:

  3. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,708
    Hi GraH - Guido,

    It is better to round off the result to whole number

    =ROUND($C$26/SUM($B$5:$B$24)*B5,0)

    Regards
    Bosco
  4. GraH - Guido

    GraH - Guido Active Member

    Messages:
    663
    Dearly noted...
  5. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    175
    Normally one might expect ROUND to introduce a discrepancy in the final total. In this instance even
    =ROUND(total/SUM(tier)*tier,-3)
    sums to full machine precision because the calculation for the (first plus last tier), (second plus penultimate etc) give the same total which is not changed by rounding.
    GraH - Guido likes this.
  6. GraH - Guido

    GraH - Guido Active Member

    Messages:
    663
    Peter, first time ever I see someone use a negative number as the last argument in round. Though obvious what it does, I never considered it doing once. It is a neat trick.
    Indeed I thought about applying rounding for a split second but, and I did not check it, I expected the result might have been slightly off because of the 15 FP precision. In my first formula I even divided each tier by 100Mio, then saw that wouldn't make any difference.:rolleyes:
    I liked Bosco's rounding because it showed the values exactly as was expected by the set formatting by the OP. And indeed total SUM was exactly the same.
    You have now explained why that is.

Share This Page