Rounding errors when a "5" is the third decimal place using formul

[Jbagger]

I am having rounding errors in Excel. When I use a formula to make a
calculation, the resulting numbers will not round correctly when a 5 is the
third decimal place. Iit always rounds down, instead of up. I can enter the
same number in another cell with no formula and it rounds correctly. This
results in very inconsistent calculations. Any ideas or fixes? Thank you!

 

[JE McGimpsey]

It's important to remember that setting a display format doesn't change
the underlying value. If you have

0.0049

in a cell set to 3 decimal places, it will display as

0.005

but if rounded to 2 digits, it will round to 0.00 rather than 0.01.

To prevent that, you could wrap your calculation with ROUND(), e.g.,:

=ROUND(<your calc here>,3)

in which case 0.0049 will be rounded to 0.005, and subsequent rounding
will round up.

 

[Jbagger]

Thank you for the replies. I have confused formatting the cells with the
ROUND function and failed to consider the "other" decimal places beyond the
third one.

My main problem is that I need to have the formulas use the rounded numbers,
rather than than original ones. These formulas are statistical (psychology)
and include Fisher R to Z and Browne's estimate for cross-validation. Thus,
I think I need to use the ROUND function for several cells across several
spreadsheets and workbooks.

Is there a way to apply ROUND to all of these cells without manually typing
it in for each cell? Thanks!

 

[BoniM]

please give formula/function used

 

[MartinW]

Hi Jbagger,

Sounds like a formatting issue, i.e. what you are seeing is 2.135 when the
true value is 2.1348927489621 or whatever.

One way around this is to go to Tools>Options>Calculation Tab and check
'Precision as Displayed'. This works but in many cases it is not a good
option as it effects every calculation in the worksheet.

Usually a better way to address the problem is to introduce the ROUND
function into your formulas. i.e. a simple formula like =A1/B1 would become
=ROUND(A1/B1,3)
there are other options depending on what you are trying to achieve but the
approach is basically the same.

HTH
Martin