Tuesday, August 23, 2005

Dividing a number equally into rounded numbers


From Exceltip.com

Problem:
The price in cell B1 must be divided accurately into 5 similar payments, with up to two decimal places.
When simply dividing the price 48.04 by 5 and rounding the result to two decimal places, we get 9.61.
However, when multiplying the result back by 5, we get 48.05, which is not the exact number we originally divided.
How could we find 5 similar numbers that sum up to the accurate price?

Solution:
In order to find our 5 similar payments, we would have to calculate each payment by equally dividing the current price (original price(-)payments already calculated) by the number of remaining payments (those that were not yet calculated).
First, we must calculate the first payment, by dividing the price by 5 and rounding the result:
=ROUND(B1/5,2)
After finding the first payment, we can calculate all the remaining payments by using the following formula:
=ROUND(($B$1-SUM($B$2:B2))/(7-ROW()),2)

Total Price $48.04

Payment 1 $9.61
Payment 2 $9.61
Payment 3 $9.61
Payment 4 $9.61
Payment 5 $9.60


Posted by: Jethro | 7:11 PM |