Excel Rounding

  • Thread starter Thread starter Jeff Westbrook
  • Start date Start date
J

Jeff Westbrook

I have been working on a spreadsheet for several hours,
and I am stumped. I am not sure what I am missing.

Needless to say, this spreadsheet has several very basic
formulas (i.e., cell B5 - cell B6, etc) and adding cells
that are all formulas.

When I am trying to do a comparison, the #'s do not equal
due to what appears to be rounding issues. I am confused
as I do not think there should be any rounding.

Here is an example. I am, via a formula, taking 16,466.5 -
16,449.3. By everything I know, this should equal 17.2.
Excel is showing this value to be 17.2000000000007. If I
try and round up to the nearest 10th, this comes out to be
17.3.

Can someone tell me what I am missing? How in the world
can that value be correct?

Thank you,
 
It could be that the numbers you are calculating have themselves been
calculated with long hidden decimal places.

From recall I think your best bet is to:

Back up your work.....select: Tools>Options>Calculation and then check
the box with 'Precision as Displayed'. This should then only actually
calculate the visible data on your sheet, but IT CANNOT BE UNDONE, so
save your workbook and open a copy first to see if it gives you what
you want.

Good luck!
 
It is a rounding issue - one that can't be helped with the way XL
and every other spreadsheet calculate. You can get a technical
explanation here:

http://cpearson.com/excel/rounding.htm

The quick and dirty answer is that just as 1/3 cannot be exactly
represented in decimal form (i.e., 0.333...), so most fractional
values cannot be exactly represented in binary.

One fix would be to use ROUND (not ROUNDUP):

=ROUND(B5-B6,1) ==> 17.2
 
The no.of decimal places must have selected as 13. Change
the decimal places to 1 [format-cells-number-decimal
places]
 
Formats change the display, but not the underlying value, and therefore
do not solve this problem unless you also set "Precision as Displayed"
under Tools|Options.

J.E. McGimpsey has given the correct answer. The OP's difficulty is due
to the fact that neither .3 nor .2 can be exactly represented in binary
and so must be approximated. This results in a visible "discrepancy"
because the OP subtracted away 3 significant figures.

Jerry

Krishnadas said:
The no.of decimal places must have selected as 13. Change
the decimal places to 1 [format-cells-number-decimal
places]
-----Original Message-----
I have been working on a spreadsheet for several hours,
and I am stumped. I am not sure what I am missing.

Needless to say, this spreadsheet has several very basic
formulas (i.e., cell B5 - cell B6, etc) and adding cells
that are all formulas.

When I am trying to do a comparison, the #'s do not equal
due to what appears to be rounding issues. I am confused
as I do not think there should be any rounding.

Here is an example. I am, via a formula, taking 16,466.5
-

16,449.3. By everything I know, this should equal 17.2.
Excel is showing this value to be 17.2000000000007. If I
try and round up to the nearest 10th, this comes out to be
17.3.

Can someone tell me what I am missing? How in the world
can that value be correct?

Thank you,
Click to expand...
Click to expand...
 
Back
Top