Dividing an Amount into Equal Payments While Avoiding Division / Rounding Errors

Problem:

The price in cell B1 must be divided accurately into 5 similar payments.
When simply dividing the price $48.04 by 5 and rounding the result to two decimal places, we get individual payments of $9.61.
However, this would add up to a total payment of $48.05, which is not the exact amount we started with.
Therefore, we want to calculate 5 similar numbers that add up to the total amount.

Solution:

Step 1:
Calculate the first payment by dividing the price by 5 and rounding the result.
Use the ROUND function in the following formula:
=ROUND(B1/5,2)

Step 2:
Calculate each remaining payment.
Use the ROUND, SUM, and ROW functions in the following formula:
=ROUND(($B$1-SUM($B$2:B2))/(5-ROW()+ROW($B$2)),2)
Screenshot // Dividing an Amount into Equal Payments While Avoiding Division / Rounding Errors
Dividing an Amount into Equal Payments While Avoiding Division / Rounding Errors

Users are saying about us...

  1. pokemon onesie charmander

    I do trust all of the ideas you have introduced for your post. They're really convincing and can certainly work. Still, the posts are very short for beginners. May just you please extend them a bit from next time? Thank you for the post.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube