dividing currency as equally as possible

H

Healingbear

I'm trying to figure out a function/formula to calculate how to dispurse
say $10.00 between 3 cells as equally as possible.

IF A1 = $10.00, and B1, C1, and D1 each equal A1/3,
then each get $3.33 equalling $9.99, when in fact I need two of them to
get $3.33 and one of them to get $3.34 for the accurate total of
$10.00.

Financially, I MUST account for each penny! Interestingly, it's not
important which cell gets which amount!

Any help would be appreciated!
 
S

Stephen Bye

You could just set D1 to A1-B1-C1. For division of an amount into 3 parts
this should be accurate enough, but for the division of an amount into more
parts, to spread it as evenly as possible you need to use the 'reducing
balance' method. In each cell, calculate the amount remaining divided by the
number of cells remaining and round it.
So:
B1 will be =ROUND(A1/3,2)
C1 will be =ROUND((A1-B1)/2,2)
D1 will be =(A1-B1-C1) (or ROUND((A1-B1-C1)/1,2), which will give the
same result).
 
Q

Quark

OK this should do it, A1 = $10

B1 = Rounddown(($A$1)/3,2) = 3.33
C1 = Rounddown(($A$1)/3,2) =3.33
D1 = Roundup(($A$1)/3,2) =3.34
=10

Hope that helps

Quarkey
 
Top