Results 1 to 5 of 5
Thread: rounding convention

20100225, 17:31 #1
 Join Date
 May 2002
 Posts
 410
 Thanks
 1
 Thanked 0 Times in 0 Posts
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!

20100225, 18:12 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,492
 Thanks
 377
 Thanked 1,473 Times in 1,340 Posts
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!
RGMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20100226, 05:10 #3
 Join Date
 Feb 2008
 Location
 A cultural area in SW England
 Posts
 3,438
 Thanks
 33
 Thanked 196 Times in 176 Posts
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 FloatingPoint Calculations.
Knew you'd enjoy it!BATcher
"The trouble with quotes on the internet is that you can never know if they are genuine."
Abraham Lincoln

20100226, 10:19 #4
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20100226, 19:15 #5
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,492
 Thanks
 377
 Thanked 1,473 Times in 1,340 Posts
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...).
RGMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs