How to stop rounding without changing number of decimal places

M

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
 
N

Nick Hodge

You are getting the right answer! Just not the answer you want. Excel
holds data to 15 digits of precision, no matter what is displayed by means
of formatting.

The way to correct this is

1) Use the ROUND function on all your 'source' formulae.
=ROUND(SUM(A1:A100),1)
2)Tools>options...>calculation and check 'precision as displayed'. This
setting will set for the whole workbook, which may not be desirable

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top