Preventing incorrect rounding in formulas

[muckybeast]

I have some basic formulas in a spreadsheet, SUM formulas,
and have the decimal point set at one. I had a formula in
that had the number 3.6% in one cell, and that was
supposed to minus 2.7% and then give me the answer in the
cell containing the formula. The correct entry would have
been 0.9%, but it entered 1.0%. When I increased the
number of decimal points, it then changed the answer to
0.95%, still incorrect as it should have just been 0.9%.
This is very frustrating as I rely on these formulas for
work that is sent out to clients. Could somebody tell me
where I can change a setting to ensure that the correct
answer appears in the cell containing the formula? Thanks.

 

[Peo Sjoblom]

Most likely the underlying values are not the same as what you see,
for instance a value like 10.001, if you put that in A1:A100, then format
the range as number with 2 decimals, all you will see is 10.00 in each cell,
now when you sum that it will return 1000.10 although you would expect by
looking at it
would return 1000.00 I believe you are experiencing the same thing. You can
either use more decimals,
round the amounts/values or use tools>options>calculation and precision as
displayed. The last
might not be ideal since you will get discrepancies.

 

[muckybeast]

I have only been entering exact whole numbers with one
decimal point, for example, 10.0. Should I be putting in
all my whole numbers as two decimals points, i.e., 10.00
and then just changing them to one decimal point when the
work is finalized, would that work? Thanks for your
advice.

 

[Ron Rosenfeld]

I have some basic formulas in a spreadsheet, SUM formulas,
and have the decimal point set at one. I had a formula in
that had the number 3.6% in one cell, and that was
supposed to minus 2.7% and then give me the answer in the
cell containing the formula. The correct entry would have
been 0.9%, but it entered 1.0%. When I increased the
number of decimal points, it then changed the answer to
0.95%, still incorrect as it should have just been 0.9%.
This is very frustrating as I rely on these formulas for
work that is sent out to clients. Could somebody tell me
where I can change a setting to ensure that the correct
answer appears in the cell containing the formula? Thanks.

Well, although you may think you are executing 3.6% - 2.7%, the fact that you
get 0.95% means you are NOT entering those two numbers.

One or both of those numbers is being calculated, and in the calculation lies
your problem.

If you post the formula you are using, and the source of data for that formula,
we may be able to help.


--ron