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!

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!

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!

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