• 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

Peter Bartholomew

Well-Known Member
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

Well-Known Member
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.
 
Top