Rounding

[BarbHerb]

Why does Excel round correctly sometimes but not all the time when using the
"decrease decimal" function?

Take for example the number - 6.464640
When you decrease the last digit you get - 6.46464
Excel rounds correctly, 0 is less than or equal to 5 so the 4 stays as is

When you decrease the next digit - 6.4646
Excel rounds correctly, 4 is less than or equal to 5 so the 6 stays as is

When you decrease the next digit - 6.465
Excel rounds correctly, 6 is greater than or equal to 5 so the 4 rounds to 5

When you decrease the next digit - 6.46
Excel does not round correctly, 5 is greater than or equal to 5 so the 6
should round to 7 but does not

Why is that please? Thanks! Barb

 

[Rick Rothstein]

Exactly what is the "decrease decimal" function? If you mean the Cell
Formatting option of Number where you can specify the number of decimal
places, then changing the number of decimal places you see does *not* change
the underlying value in the cell... if it started as 6.46464 (no trailing
zeroes in the original value), it will still be 6.4646 after you change the
displayed format. So, 6.46464 displayed to 2 decimal places is 6.46 since
the number in the 3rd decimal place is 4.

 

[Mike H]

Hi,

Excel is correctly following math rules for decreasing decimal places.
Looking at intermediate numbers is not the way to do it so when you say
6.465
should become
6.47
you are incorrect. When Excel rounds to 2 decimal places it is doing the
rounding on the full and original number 6.46464.

As you will see the 3rd decimal is 4 so when excel rouns this to 2 decimal
places it correctly rounds to 6.46

If you start with the number 6.465 and then get Excel to round to 2 decimal
places you get what you expect for the other number 6.47.

In Excel when you apply a format you don't change the underlying value so in
your example in all the cells the number is 6.46464, it's just what you see
that is different.

Mike