| View previous topic :: View next topic |
| Author |
Message |
trsanders
Newbie
Joined: 31 Aug 2007
Posts: 2
|
 Posted: Fri Aug 31, 2007 10:09 am Post subject: Rounding error with negative numbers |
 |
|
Is the way calc rounds negative numbers correct?
As an example, if calc rounds to the nearest whole number then the following table shows how it will round tenths.
| Code: |
1.4 1.3 1.2 1.1 1.0 0.9 0.8 0.7 0.6 0.5 will all round to 1
0.4 0.3 0.2 0.1 0.0 -0.1 -0.2 -0.3 -0.4 will all round to 0
-0.5 -0.6 -0.7 -0.8 -0.9 -1.0 -1.1 -1.2 -1.3 -1.4 will all round to -1
|
As you can see, for any whole number there are 10 possible tenths that will round to the number except for zero which only has 9.
The above hold true no matter how many decimal places you are rounding to. |
|
| Back to top |
|
 |
RickRandom
Super User
Joined: 27 Jan 2006
Posts: 1065
Location: UK
|
| Posted: Fri Aug 31, 2007 12:00 pm Post subject: |
|
|
| I don't know if there is an absolute right or wrong way to round numbers, although I have always been taught to round 0.5 up, but I can't recall if I was taught -0.5 should be rounded to -1 or 0. However Calc's behaviour is consistent with Excel on my work laptop and with Gnumeric on this one. |
|
| Back to top |
|
|
Ed
Super User
Joined: 28 May 2003
Posts: 816
|
| Posted: Fri Aug 31, 2007 12:27 pm Post subject: |
|
|
What Calc does is correct.
The object of rounding to an integer is to find the nearest integer value to the number in question. You have to ask yourself:
* Is -0.51 nearer to 0 or -1? And:-1
* Is -0.52 nearer to 0 or -1? Ans:-1
* Is there any string of numbers that can possibly appear after the 5 that would make the number nearer to 0 than to -1? And:NO!
When you consider this it is obvious that if the first decimal place is 5 then the number is nearer to -1, so should be rounded to -1. |
|
| Back to top |
|
|
trsanders
Newbie
Joined: 31 Aug 2007
Posts: 2
|
| Posted: Fri Aug 31, 2007 1:07 pm Post subject: |
|
|
| Ed wrote: | What Calc does is correct.
The object of rounding to an integer is to find the nearest integer value to the number in question. You have to ask yourself:
* Is -0.51 nearer to 0 or -1? And:-1
* Is -0.52 nearer to 0 or -1? Ans:-1
* Is there any string of numbers that can possibly appear after the 5 that would make the number nearer to 0 than to -1? And:NO!
When you consider this it is obvious that if the first decimal place is 5 then the number is nearer to -1, so should be rounded to -1. |
But I'm talking about -0.5 which is exactly 0.5 away from both 0 and -1.0.
As a simple demonstration of the impact try this in calc.
ROUND(1 + N; 0) - ROUND(N; 0)
For every value of N except -0.5 the result is 1.00 When you use -0.5 the result is 2. This is the only number that will produce this result. |
|
| Back to top |
|
|
RickRandom
Super User
Joined: 27 Jan 2006
Posts: 1065
Location: UK
|
| Posted: Fri Aug 31, 2007 1:16 pm Post subject: |
|
|
| It seems to me more of a philosophical and/or mathematical question, not a Calc question.... |
|
| Back to top |
|
|
Ed
Super User
Joined: 28 May 2003
Posts: 816
|
| Posted: Fri Aug 31, 2007 1:48 pm Post subject: |
|
|
| trsanders wrote: |
But I'm talking about -0.5 which is exactly 0.5 away from both 0 and -1.0.
|
Firstly, rounding only considers the first figure after the number of places being rounded to.
Secondly, since a computer can only hold a finite number of decimal places of each number, how could it possibly know whether the number has a non-zero digit after the last digit of is able to store? If the computer only stores the number to 15 significant figures, it can't possibly know whether there is a non-zero digit at (for example) the billionth significant figure, so it has to treat the number identically whether there is or not.
Thirdly, this is all academic since no measurement is ever exactly -0.5 anyway. The only way you can ever measure an exact quantity is if you can count something, in which case the number will always be a natural number.
Last edited by Ed on Fri Aug 31, 2007 2:39 pm; edited 2 times in total |
|
| Back to top |
|
|
bobharvey
Super User
Joined: 23 Apr 2004
Posts: 1076
Location: Lincolnshire
|
| Posted: Fri Aug 31, 2007 1:57 pm Post subject: |
|
|
| trsanders wrote: | | But I'm talking about -0.5 which is exactly 0.5 away from both 0 and -1.0. |
No it isn't.
it is between 0.5 and 0.5 + the smallest value represented by a computer away from 0
It is between 0.5 and 0.5 - the smallest value away from -1.
In any case, it is on the -1 side of the line that is half way between 0 and -1 |
|
| Back to top |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 5936
Location: Kingston ON Canada
|
| Posted: Sat Sep 01, 2007 4:52 am Post subject: |
|
|
| RickRandom wrote: | | It seems to me more of a philosophical and/or mathematical question, not a Calc question.... |
I always get lost with rounding rules but there is (are?) an ISO standard re this topic. See http://www.iop.org/EJ/article/0957-0233/11/12/302/e01202.pdf which I think discusses some of the issues.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.2.0 |
|
| Back to top |
|
|
|
