1. I have a question on what is the "correct" rounding convention.

In Excel, 24.15 becomes 24.2 when rounded or when formatted to 1 decimal place.

Another program I am using rounds the numbers somewhat differently. When rounding to the tenth decimal place, everything from x.0 up to and equal to x.5 is rounded down. Everything above x.5 is rounded up.
For example, 24.15 is rounded down to 24.1, but 24.150000001 is rounded up to 24.2.

As long as I am aware of it, it's ok, but I'm curious as to whether there is an "official" convention.
thanks!

2. J.E.

It has always been my understanding that .5 goes up to the next digit, i.e.,
.05 = .1
.005 = .01, etc.
and < .5 goes down, i.e.,
.4 = 0
.014 = .1
.0114 = .011, etc.

Excel follows this method.

However, beware of the formatting trap!
Formatting a number to one decimal place appears to round it but
calculations will use the whole number!

In the following example Col A & C are formatted as 2 Decimal Places
Col B is formatted as 1 Decimal Place
B1 formula = "=A1", ect
C1 formula = "=Round(A1,1)"

You can see the difference in the sums!

RG

3. Originally Posted by jepalmer
When rounding to the tenth decimal place, everything from x.0 up to and equal to x.5 is rounded down. Everything above x.5 is rounded up.
You might be getting problems with the precision in which numbers are held and/or the conversion between the internal 'binary' form and the decimals which humans understand! "Tenth decimal place" may be outside the capabilities of that program...

A couple of Wikipedia articles: Accuracy and Precision, and Floating Point, and something fascinating from Microsoft: INFO: Precision and Accuracy in Floating-Point Calculations.

Knew you'd enjoy it!

4. There are various rounding techniques. Some round up on even digits and down on uneven digits (I forgot which way around). So:

0.15 becomes 0.1
0.25 becomes 0.3
0.35 become 0.3
0.45 becomes 0.5

Excel rounds up if the next digit is 5 or more.

5. Originally Posted by BATcher
You might be getting problems with the precision in which numbers are held and/or the conversion between the internal 'binary' form and the decimals which humans understand! "Tenth decimal place" may be outside the capabilities of that program...
You're absolutely correct. This has been a know problem with all spreadsheets I've ever used starting w/VisaCalc (showing my age).
I still don't understand why Microsoft doesn't provide a Binary Coded Decimal (BCD) option in Excel. With this form of encoding of numbers there is true representation of base 10 decimals. Which IMHO is a basic necessity for calculations involving dollars and cents where absolute accuracy is required (that's the old accountant in me speaking...).

RG

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•