• 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...

  • 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

slstewart249

New Member
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.
 

Attachments

  • 2018-06-13 Royalty Model.sls.xlsx
    10.9 KB · Views: 5
Perhaps, in C5 =$C$26/SUM($B$5:$B$24)*B5 and drag down.
 

Attachments

  • 2018-06-13 Royalty Model.sls.xlsx
    10.8 KB · Views: 5
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.
 
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.
 
Back
Top