# 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

• 10.9 KB Views: 5

#### GraH - Guido

##### Well-Known Member
Perhaps, in C5 =\$C\$26/SUM(\$B\$5:\$B\$24)*B5 and drag down.

#### Attachments

• 10.8 KB Views: 5

#### bosco_yip

##### Excel Ninja
Perhaps, in C5 =\$C\$26/SUM(\$B\$5:\$B\$24)*B5 and drag down.
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

#### GraH - Guido

##### Well-Known Member
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
Dearly noted...

#### 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

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