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

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!

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

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.


In article ,
Jbagger wrote:

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!

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

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!

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

please give formula/function used

"Jbagger" wrote:

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!

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

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 ToolsOptionsCalculation 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