Formulas do not add up to original value

[Amy6514]

I have a spreadsheet that works out how much of a set amount should be
divided between different cells. However the figures it works out then do not
add up to the original amount that I started with. i.e. it splits 160 into
40.31, 39.45, 36.69 and 43.56 - which addes up to 160.01. How do I stop that!?

 

[Niek Otten]

You don't give us any formulas, so it is difficult to tell what might be
wrong.
Anyway, the addition you give as an example is perfectly OK!

Please post values, formulas, expected and acrtual results so we might be
able to help

 

[zvkmpw]

I have a spreadsheet that works out how much of a set amount should be
divided between different cells. However the figures it works out then donot
add up to the original amount that I started with. i.e. it splits 160 into
40.31, 39.45, 36.69 and 43.56 - which addes up to 160.01.
How do I stop that!?

Rounding happens all the time when one keeps a fixed number of decimal
places, and folks tend to live with it.

If it's really important that the sum of the rounded parts equals the
original amount, then here's one way to do it.

Suppose the four parts are formula_1 in A1, formula_2 in A2, formula_3
in A3, formula_4 in A4; with the original amount in B1.

First replace formula_1 in A1 by
=ROUND(formula_1, 2)

Likewise for A2 and A3.

Then replace formula_4 in A4 by
= ROUND(B1, 2)-A1-A2-A3

 

[Jim Thomlinson]

Check out this link...

http://www.mcgimpsey.com/excel/pennyoff.html