Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Moneo on August 16, 2003, 09:12:01 PM In the last couple of days in the challenge regarding "useful programs", Oracle posted the following function to perform rounding to the nearest whole number:
Code: DEF FNround# (m#) = SGN(m#) * INT(ABS(m#) + .5) (I took the liberty of streamlining it for the general case.) I recognized immediately that this was the most precise rounding logic that I had ever seen, because usng the ABS it rounds correctly without being burdened by the sign of the input number. Then, it restores the sign by using the SGN. After testing Oracle's logic thoroughly, I realized that for at least the last 5 years, I have been rounding negative numbers incorrectly. :oops: BTW: Ak00ma came up with the following enhancement of Oracle's logic, which handles numbers with implied decimal places: Code: DEF FNround# (m#, dec%) = SGN(m#) * INT(10^dec% * ABS(m#) + .5) / 10 ^ dec% Without much ado, I'll list 3 erroneous rounding methods: 1) Using the CINT. This looks very simple when your numbers to be rounded are in the range of integer values. However, if you round .5 or .5 you get zero for both. Who knows why, and what other inputs will also fail? 2) Testing the sign and rounding by +.5 or .5 accordingly. This was my method, but testing shows that it doesn't always work for all negative inputs. The root of the problem is stated in the QB manual: "The INT returns the largest interger less than or equal to to the inputš. Less than/equal does not work the same for positive and negative numbers. Another Basic weirdness. 3) The cavalier method. Just do: INT(NUMB+.5) and hope for the best. Works great for positive numbers, but suffers from obvious problems on negative numbers, compounded by the above less than/equal weirdness. SUMMARY: Don't take my word for it, do your own testing. You will certainly come to realize that Oracle's approach is the only correct way to round. BTW: Tested the INT on a negative number in Visual Basic, and got the same error. INT(123.4) gave 124. :rotfl: ***** _________________ Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: pr0gger on August 17, 2003, 12:34:08 AM Code: RoundedInteger% = UnroundedSingle! Works most of the time for me, but it has obvious limits. Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Moneo on August 17, 2003, 01:05:12 AM PROGGER:
You're right, it works *most* of the time, and even handles negative numbers. I just tested it and it has the same problem as a CINT; that is, an input of .5 or .5 returns a zero. It probably uses the same internal "Convert to INTeger" logic. BTW: Many years ago I had all kinds of problems with CINT. I don't recall what the other problems were, other than the .5 bug, but I decided back then NEVER to use the CINT again. I don't know what other "obvious limits " you're referring to. ***** Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Neo on August 18, 2003, 09:47:46 AM Don't know what the obvious limits are!?!?!?!?
You try this: Code: RoundedInteger% = UnroundedSingle! When UnroundedSingle! is a very big or very small negative number, like >32767 and <32768. Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: pr0gger on August 18, 2003, 12:31:23 PM Quote from: "Neo" Don't know what the obvious limits are!?!?!?!? You try this: Code: RoundedInteger% = UnroundedSingle! When UnroundedSingle! is a very big or very small negative number, like >32767 and <32768. ^   What he said :) Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Moneo on August 18, 2003, 04:11:00 PM You guys are right. Trying to stuff a number outside of an integer range into an integer variable  this is pretty obvious. Just like an old saying: "You can't get 10 pounds of sh*t into a 5 pound bag".
***** Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Antoni Gual on August 18, 2003, 05:38:20 PM I'm in vacation, don't expect me to follow this thread...
MS says in its Knowledge base that INT(.5) giving a result of 0 is not a bug, it's just the correct result according to IEEE standard... Edited: Sorry, wrong quote... Code: DOCUMENT:Q23389 21FEB1991 [B_QUICKBAS] TITLE :Unexpected Result from CINT(.5) in QB87; IEEE Rounds to Even PRODUCT :Microsoft QuickBASIC Compiler PROD/VER:3.00 OPER/SYS:MSDOS KEYWORDS:B_BasicCom Summary: The coprocessor version of QuickBASIC version 3.00 (QB87.EXE) returns 0 (zero) in the following example (as do QuickBASIC versions 4.00, 4.00b, and 4.50; Microsoft BASIC Compiler versions 6.00 and 6.00b; and Microsoft BASIC Professional Development System (PDS) versions 7.00 and 7.10, which all use the IEEE floatingpoint format): PRINT CINT(1/2) PRINT CINT(.5) PRINT CINT(1.0/2.0) Three zeros (0) are printed. This differs from the noncoprocessor version of QuickBASIC version 3.00 (QB.EXE) and earlier versions, which instead prints three ones (1). Compare this with the fact that PRINT CINT(1.5) returns 2 in both the coprocessor and noncoprocessor versions of QuickBASIC version 3.00 (and in the other more recent products listed above). The observed rounding difference in the first case above is not caused by any bug. The IEEE standard dictates that rounding of x.5 occurs to the even integer nearest to x, for example, CINT(.5)=0, CINT(1.5)=2, CINT(2.5)=2, CINT(3.5)=4, CINT(4.5)=4, etc. The noncoprocessor version of QuickBASIC 3.00 (QB.EXE) supports a different floatingpoint format, the Microsoft Binary format (MBF), which rounds differently than the IEEE standard. End of edit Second edit: I erased the wrong quote because it was about QB for MacIntosh :o But it said an interesting thing : Code: The purpose of this behavior is to prevent an average upward (or downward) bias as various calculations are rounded. If the number was always rounded up, there would be an upward bias in calculations. Rounding to the nearest even number averages out; therefore, no rounding bias occurs. End of second edit Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: oracle on August 18, 2003, 05:41:19 PM Or we could just use the common maths standard, round >=.5 up and <.5 down.
Anyone up for making a swedish rounding function? Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Sterling Christensen on August 18, 2003, 06:09:36 PM INT(number + .5) seems to be mathematically correct. At ±???.5 is always rounds up (toward positive infinity). In those cases, your formula always rounds away from zero. It just depends what you want.
Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Moneo on August 18, 2003, 06:56:15 PM ANTONI,
Very interesting and informative. However, no matter how much authority the IEEE has, these rounding principles are not a global industry standard, and would never be accepted by any CPA or accountant. In accounting practices, if you round 2.5 you should get 3 not 2. However, in analysing the attached document that you provided, this IEEE rounding phenomenon only occurs in QB when using the CINT function or when assigning a value to a integer variable. If you use the "normal" way of rounding, which is to add .5 to the positive floating point number and then take the INT of the result, then the above IEEE standard has no effect at all. That is, the values will always be rounded up. On the other hand, if your program is working and rounding values that are to be used in an IEEE related application, then you should consider emulating the IEEE standard of rounding. PS: Have a nice vacation, Antoni. Thanks for thinking of us. :wink: ***** Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Moneo on August 18, 2003, 08:09:26 PM I'm using QuickBasic 4.5 which after testing I found that it does use the IEEE floatingpoint format. Here's what I found:
Code: 'ASSIGNMENT A OF FLOATING POINT NUMBER TO AN INTEGER: R% = .5 'The result is 0 .........Expected result is 1 R% = 2.5 'The result is 2..........Expected result is 3 'USE OF CINT FUNCTION: R% = CINT(.5) 'The result is 0 .........Expected result is 1 R% = CINT(2.5) 'The result is 2 .........Expected result is 3 So, if you are using any of the mentioned versions of QuickBasic or QB, and you DO NOT want the IEEE floatingpoint format to be applied to your arithmetic: 1) Don't directly assign floatingpoint numbers to an integer variable if you expect normal, accounting procedure rounding. Perform a standard rounding procedure on the floatingpoint number, and then assign the result to your integer variable. 2) Don't use CINT. "A word to the wise is sufficient". ***** Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Moneo on August 27, 2003, 12:44:35 AM CONCLUSIONS ON THE ROUNDING ISSUE:
I spent about 1620 hours looking on the web for stuff about rounding. I found 8 rounding modes. One of them is called "Roundhalfup" and is also known as "conventional rounding". This is the one that Oracle and I use. I discovered that countries, government agencies, and corporations all do rounding in different ways. The reason: they want to handle MONEY in the manner that best suits them. Therefore, there is no "standard" accounting method of rounding. The programming solution is: Find out if the project has any special rounding or truncating requirements. If so, get specific, detailed specifications, and write special functions accordingly. If not, obtain written authorization, and use Oracle's "conventional rounding" function, as follows: Code: DEF FNround# (m#) = SGN(m#) * INT(ABS(m#) + .5) Never use CINT, FIX, or INT for the purpose of rounding. They use nonconventional rounding rules.  EDIT  BTW: Here's 8 rounding methods that I was able to find: 1] Roundhalfeven: AKA Bankers Rounding, AKA odd/even rule, AKA in the USA as roundtonearest for certain applications. This rounds 2.5 to 2, and 3.5 to 4. 2] Roundhalfup: AKA conventional rounding, AKA arithmetic rounding, AKA roundtonearest in Europe, International Finance, and in the US for taxes, particularly the IRS. This rounds 2.5 to 3. We will also call this "The Oracle method". 3] Rounddown: AKA truncate. Did find some obscure usages. This rounds 1.9 to 1. ***For the following 5 types, found no usages: 4] Roundceiling: towards + infinity 5] Roundfloor: towards  infinity 6] Roundup: 1.1 goes to 2. 7] Roundhalfdown: Similar to Roundhalfup by .5 goes down. 8] Multistepgradualrounding: by IEEE ***** Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Blitz on September 02, 2003, 04:30:37 AM int() cuts the fraction off, cint() rounds. And when you cast a float to integer or long in qb, qb uses cint
Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Antoni Gual on September 02, 2003, 06:31:25 AM I think the way CINT rounds is perfectly valid. Rounding .5 to the nearest even integer will no make any difference in graphics or whatever. The strange behavior of CINT has a logical reason to be . You only need to worry if you are doing an accounting program, in this case the rules are different from QB's
Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Moneo on September 02, 2003, 03:17:59 PM BLITZ,
1) Yes, CINT rounds, but it uses the "roundhalfeven" method which is not what everyone considers as normal rounding. Example: 2.5 goes to 2, and 3.5 goes to 4. 2) When CINT rounds negative numbers, it uses the "roundhalfeven" method on the absolute value, and then changes the sign to negative. This is a variation all its own. 3) Using an INT or assigning a float to an integer give the same results. 4) An INT doesn't always "cut the fraction off". That's true for positive numbers, but for negatives, the absolute value is rounded up to the next whole value.  ANTONI, The way CINT rounds may be perfectly valid for some applications, but obviously not for all. That's why I recommend not using it and instead implementing the exact rounding method required by the application. Please take a look at this Microsoft page for some interesting rounding variations even within their own languages. http://support.microsoft.com/default.aspx?scid=kb;enus;Q196652 ***** Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Plasma on September 02, 2003, 03:37:42 PM You spent 20 hours looking for stuff about rounding?!....damn, and I thought I wasted a lot of time...
Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Moneo on September 02, 2003, 05:55:41 PM Plasma,
Actually I've been investigating the rounding issue for about 3 weeks now. It has become my pet project. If you consider the millions of rounding operations that are performed daily, and some of them erroneously, it is not a trivial matter. I feel that learning about this is not a waste of time. Wouldn't you agree? ***** Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Plasma on September 02, 2003, 06:59:49 PM No, I wouldn't. There ain't that much to it. .5 or higher, you round up. Otherwise you round down.
Whatever computers do is just because it's more efficient. For example, 1 KB is not 1000 bytes, but 1024 bytes. It *should* be 1000 bytes, but it's not. Just like 2.5 *should* round to 3, but it doesn't. I don't think you need to spend 3 weeks to figure this out. Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: oracle on September 02, 2003, 08:20:33 PM I disagree with plasma. 1024 bytes has a good reason to be 1024 bytes (^2). But there is no reason why programmers can't round properly in their programs, that's just sloppy. Admittedly rounding normally makes such a small difference that it's not noticeable, but that's no reason to not do it correctly. If you are going to use a method of rounding supplied to you by a computer, you should know how it rounds, and if you don't like it, then make your own rounding function, that's all I'm saying.
Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: LinkMaster Sab on September 02, 2003, 09:19:56 PM It's not too complicated to make your own rounding function, so just make one if it matters :D. I didn't realize there were errors in the code, so now I think I'll start writing one for using regularly.
Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Plasma on September 02, 2003, 10:04:44 PM Oracle, there is a very good reason: It's faster. :)
(And it's not the "programmers" who are sloppy; the rounding is done in the FPU.) Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: SCM on September 02, 2003, 10:07:07 PM It's not errors in the code, it is just a different method intended to avoid bias. The important thing is knowing how it behaves, so that your program will do what you expect it to do.
Code: INT(x) rounds to the nearest integer at or below x (the floor) INT(x) rounds to the nearest integer at or above x (the ceiling) INT(x + .5) rounds up for .5 and up down for under .5 CINT rounds .5 to nearest even number otherwise to the nearest number SGN(x) * INT(ABS(x) + .5) rounds .5 away from zero, otherwise to the nearest number Moneo, I use the ceiling function for color assignment in my fractals, and I often use the floor (INT) but I can't think of a specific example right now. They do have their uses, and are recognized mathematical functions. Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: oracle on September 03, 2003, 01:43:55 AM Plasma: Faster, eh? I don't doubt you, but don't you think that big software companies should provide the .5 up method as an inbuilt function? Because that's the way much of the world understands rounding...
SCM: INT(RND * num) + 1 :) Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Plasma on September 03, 2003, 02:26:03 AM Is it really that hard to add .5?
Code: DEF FNround (number) = INT(number + .5) C'mon, there's no arcsine function either, but nobody's whining about that. Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: oracle on September 03, 2003, 03:34:15 AM That doesn't work for negatives :) That's the problem that is solved by my function in the very first post, you're a wee bit behind :)
And Glenn has an arcsine function :) If he's alive, that is ;) Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Plasma on September 03, 2003, 09:15:30 AM Er, I know that...and I know other people have written arcsine functions. The point is that QB doesn't have EVERY math function, and it shouldn't.
Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Moneo on September 03, 2003, 04:12:33 PM ORACLE and SCM:
You 2 guys have shown that you really understand the issue of various rounding methods, and how to determine which you should use. Thanks for sharing your thoughts and experience. PLASMA: Before you go making offthewall remarks, you should first read up on the rounding subject, and do some testing of the functions provided in QB with both positive and negative numbers. Then you'll be in a position to voice an opinion. For starters, read the following Microsoft page regarding rounding: http://support.microsoft.com/default.aspx?scid=kb;enus;Q196652 ***** Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Plasma on September 03, 2003, 05:08:59 PM MONEO:
I don't think any of my remarks are off the wall. I know how QB's functions work. Just because I didn't waste 3 weeks of my life researching rounding doesn't mean I don't know anything. (Don't think that because Glenn's not here you get promoted to Captain Badass. :P) Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Moneo on September 03, 2003, 06:00:47 PM Quote from: "Plasma357" MONEO: I don't think any of my remarks are off the wall. I know how QB's functions work. Just because I didn't waste 3 weeks of my life researching rounding doesn't mean I don't know anything. (Don't think that because Glenn's not here you get promoted to Captain Badass. :P) PLASMA: First of all, you jumped into this thread 17 days late. The following are 3 of your remarks which apparently don't show much understanding about rounding nor how QB functions work. Also, your insistent criticism of my spending 3 weeks researching rounding, I thing makes you a better candidate for the "Captain Badass" title. 1) You spent 20 hours looking for stuff about rounding?!....damn, and I thought I wasted a lot of time... 2) There ain't that much to it. .5 or higher, you round up. Otherwise you round down. 3) Is it really that hard to add .5? ***** Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Plasma on September 03, 2003, 06:16:56 PM First of all, I wasn't aware that I had to reply on the first day a message was posted for my opinions to be considered valid.
Second, I find nothing wrong with those statements that you think are "off the wall": 1) is a joke and it's my opinion 2) is the "common" method of rounding 3) was a response to oracle complaining about QB not having a rounding function that follows his rules And none of those statements have anything to do with QB's functions, so I fail to see how you think that my understanding of QB's functions are lacking. (Thus making you, sir, Captain Badass.) Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Moneo on September 03, 2003, 07:30:48 PM PLASMA:
Ok, let's not make this into a personal issue. To review the 3 points: 1) I got started off on the wrong foot on this one because I didn't realize it was a joke. Sorry. 2) The "common" method turns out not so common when you use CINT and INT to round, specially for negative numbers. A programmer who's thinking in terms of the "common" method and who does not fully test using CINT and INT, will come to a rude awakening when the user complains that the rounding is not "common". 3) I misunderstood you on this one. In summary, joke or no joke, in all honesty I don't feel that your participation in the thread added any insight, contribution, or better undersanding of the subject for those of us involved. That's why I labelled your comments as "offthewall". I was reacting defensively. If I offended you in any way, I apologize. If you want to consider me as "Captain Badass", that's your choice. ***** Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: Plasma on September 03, 2003, 11:13:37 PM All right, all right, sorry about the badass stuff. :)
My first post was just supposed to be funny, but I see now that it probably offended you... And as for the misunderstandings...all I can say is I hate nonthreaded boards. Although, I do think I presented a few worthwhile points: QB rounds the way it does because it uses the FPU, which rounds the way it does because it's faster and cheaper. It's not really a "flaw with QB". And secondly, while QB is very generous in the math functions it provides, it still does not have many functions builtin. So I don't think that a lack of a "common" rounding function is that big a deal, especially when you can write one with one line of code. ....and that's all I have to say about that. Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: LinkMaster Sab on September 03, 2003, 11:57:08 PM Alright Forrest ;).
It does just about any math function I know, so that'd be through Geometry so far... its doing pretty good :D. I'm in 10th grade, just so you don't think I've only done Geometry throughout my school time :D. Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: oracle on September 04, 2003, 06:15:09 PM Ahhh well... if you want to round properly, just make a function for it. At least now some people know the pitfalls of using inbuilt rounding functions, and since they cannot change there is not too much point worrying about it...
Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: LinkMaster Sab on September 04, 2003, 07:08:54 PM Really... I didn't know about those pitfalls until this post. Thanks.
Title: ROUNDING NUMBERS: Which method do you use? SEE MANY METHODS Post by: oracle on September 04, 2003, 07:17:41 PM That sums it up nicely, don't you think? :lol:
