Rounding issues
Randy found that splitting $4.33 by three percentages in IDS 11.10 gave parts summing to $4.32: ROUND(4.33*.615385,2) returns 2.66, not the 2.67 his calculator gave. Respondents (Palmer, Mills, Kagel) explained this isn't a bug — Informix rounds correctly (2.66461705 < 2.665), while his calculator was doing successive/multi-pass rounding. Advice: do calculations at higher precision (e.g. cast MONEY to DECIMAL(32,16)) before rounding, or add 0.0005 before rounding, or write a custom rounding UDR; but no algorithm removes the leftover-penny problem, so the application must decide where to plug the odd cent (e.g. last line or a balancing entry).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS: 11.10.FC3 OS: RHEL 5.7 I am working with money and doing calculations of small dollar amounts x multiple small percentages and having rounding issues getting the sum of the parts to equal the original total. I have looked at the manual for the round function and it appears that this is normal functionality, but doesn't match, in my opinion, on how rounding should be performed. Example: I need to split $4.33 into 3 component parts and I have 6 digits of precision for my split percentages. 4.33 x .615385 4.33 x .307692 4.33 x .076923 ------------ 1.0 Current server calculations: 4.33 x .615385 = 2.66 4.33 x .307692 = 1.33 4.33 x .076923 = .33 --------- 4.32 so I am a penny off In doing this on a calculator: 4.33 x .615385 = 2.66461705 which rounds to 2.67 when calculator set to round,2 and what I expected. On the server: ROUND(4.33 x .615385,2) or (4.33 x .615385)::money(10,2), which is what I am doing, gives the value as 2.66 instead of the expected 2.67 Doesn't make a whole lot of sense to me as round,3 gives 2.665 which is rounding the 4 to a 5 in the thousandths place based on the 6 in the ten thousandths place. Why doesn't it round the same way for the hundredths place? In doing more investigation, I found that if I do round((4.33 x .615385),3)::money(10,2) gives me 2.67 Not sure I have much faith in Informix's calculations now... If my answer is 2.664444445, I would still expect this to round to 2.67, but even my 'cheat' of round,3::money(10,2) would return 2.66 Is this a bug? Is it really supposed to function like that? With the functionality as currently happening, I almost always have a penny problem when I have to split small amounts between multiple accounts. The manual shows: Expression: ROUND (24,536.8746, -2) = 24,500.00 ROUND (24,536.8746, 0) = 24,537.00 ROUND (24,536.8746, 2) = 24,536.87 I understand the first 2, but the last one, again in my opinion, should be 24,536.88. TIA, Randy
My math teacher back in elementary school taught me "when rounding if the next digit is 5 or larger it goes up, if the next digit is 4 or less it goes down" Simplifies if for kids. In real world, if it is more then half, it goes up less then half it goes down and exactly half we go up. From your example: 4.33 x .615385 = 2.66461705 .00461705 is less then half of .01 so it goes down. Your calculator is incorrect according to math teachers. As a side, if you had a dollar in pennies and wanted to give it in equal parts to three people what would you give each one? Where would the extra penny go? How would you do that in a program? These are the problems of money not being just numbers but having only 2 places ( here in the US ) numbers must be forced into pennies someone gets more someone gets less, all fractions of a penny must go somewhere. George. From: "Kennedy, Randy" <RKennedy@scottsdaleaz.gov> To: ids@iiug.org Date: 09/22/2011 12:43 PM Subject: Rounding issues [25010] Sent by: ids-bounces@iiug.org IDS: 11.10.FC3 OS: RHEL 5.7 I am working with money and doing calculations of small dollar amounts x multiple small percentages and having rounding issues getting the sum of the parts to equal the original total. I have looked at the manual for the round function and it appears that this is normal functionality, but doesn't match, in my opinion, on how rounding should be performed. Example: I need to split $4.33 into 3 component parts and I have 6 digits of precision for my split percentages. 4.33 x .615385 4.33 x .307692 4.33 x .076923 ------------ 1.0 Current server calculations: 4.33 x .615385 = 2.66 4.33 x .307692 = 1.33 4.33 x .076923 = .33 --------- 4.32 so I am a penny off In doing this on a calculator: 4.33 x .615385 = 2.66461705 which rounds to 2.67 when calculator set to round,2 and what I expected. On the server: ROUND(4.33 x .615385,2) or (4.33 x .615385)::money(10,2), which is what I am doing, gives the value as 2.66 instead of the expected 2.67 Doesn't make a whole lot of sense to me as round,3 gives 2.665 which is rounding the 4 to a 5 in the thousandths place based on the 6 in the ten thousandths place. Why doesn't it round the same way for the hundredths place? In doing more investigation, I found that if I do round((4.33 x ..615385),3)::money(10,2) gives me 2.67 Not sure I have much faith in Informix's calculations now... If my answer is 2.664444445, I would still expect this to round to 2.67, but even my 'cheat' of round,3::money(10,2) would return 2.66 Is this a bug? Is it really supposed to function like that? With the functionality as currently happening, I almost always have a penny problem when I have to split small amounts between multiple accounts. The manual shows: Expression: ROUND (24,536.8746, -2) = 24,500.00 ROUND (24,536.8746, 0) = 24,537.00 ROUND (24,536.8746, 2) = 24,536.87 I understand the first 2, but the last one, again in my opinion, should be 24,536.88. TIA, Randy ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Randy- Basic math: 2.66461705 < 2.665, so it rounds to 2.66 which is right. 24,536.8746 < 24,566.875, so it rounds to 24,566.87, which is also right. Say you're dividing a dollar three ways, 3x = $1. You can calculate the first two, then subtract them from the original value to get the third: $0.33, $0.33, $0.34 If you want to more accurately dispose of the spare penny, find the value with the largest 1000ths digit and apply it there. --EEM >-----Original Message----- >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of >Kennedy, Randy >Sent: Thursday, September 22, 2011 12:43 PM >To: ids@iiug.org >Subject: Rounding issues [25010] > >IDS: 11.10.FC3 >OS: RHEL 5.7 > >I am working with money and doing calculations of small dollar amounts x >multiple small percentages and having rounding issues getting the sum of >the parts to equal the original total. >I have looked at the manual for the round function and it appears that >this is normal functionality, but doesn't match, in my opinion, on how >rounding should be performed. > >Example: >I need to split $4.33 into 3 component parts and I have 6 digits of >precision for my split percentages. >4.33 x .615385 >4.33 x .307692 >4.33 x .076923 > >------------ > >1.0 > >Current server calculations: >4.33 x .615385 = 2.66 >4.33 x .307692 = 1.33 >4.33 x .076923 = .33 > >--------- > >4.32 so I am a penny off > >In doing this on a calculator: >4.33 x .615385 = 2.66461705 which rounds to 2.67 when calculator set to >round,2 and what I expected. > >On the server: >ROUND(4.33 x .615385,2) or (4.33 x .615385)::money(10,2), which is what >I am doing, gives the value as 2.66 instead of the expected 2.67 > >Doesn't make a whole lot of sense to me as round,3 gives 2.665 which is >rounding the 4 to a 5 in the thousandths place based on the 6 in the ten >thousandths place. >Why doesn't it round the same way for the hundredths place? > >In doing more investigation, I found that if I do round((4.33 x >..615385),3)::money(10,2) gives me 2.67 Not sure I have much faith in >Informix's calculations now... >If my answer is 2.664444445, I would still expect this to round to 2.67, >but even my 'cheat' of round,3::money(10,2) would return 2.66 > >Is this a bug? Is it really supposed to function like that? With the >functionality as currently happening, I almost always have a penny >problem when I have to split small amounts between multiple accounts. > >The manual shows: >Expression: >ROUND (24,536.8746, -2) = 24,500.00 >ROUND (24,536.8746, 0) = 24,537.00 >ROUND (24,536.8746, 2) = 24,536.87 > >I understand the first 2, but the last one, again in my opinion, should >be 24,536.88. > >TIA, >Randy > > >************************************************************************ >******* > Forum Note: Use "Reply" to post a response in the discussion forum.
Hello. From my developer experience, I had some tax roundings varying from a simple ROUND() call, between 4gl results and Natural/Adabas result. What I did to equal things? I had to round my numbers, digit for digit, from last to first. I don´t know if you understood. Eg: 2.66461705 (original number - in Informix): 1) first round operation result 2.6646171 2) second.... 2.664617 3) third.... 2.66462 4) fourth... 2.6646 ... and so on, until my desired precision, for matching values. (But I did it using 4gl, I think it´s not hard for you to do it on your query, too) ;) Hope it helps. Regards. Em 22/09/2011 14:41, George_Palmer@aotx.uscourts.gov escreveu: > My math teacher back in elementary school taught me "when rounding if the > next digit is 5 or larger it goes up, if the next digit is 4 or less it > goes down" > > Simplifies if for kids. > > In real world, if it is more then half, it goes up less then half it goes > down and exactly half we go up. > > > From your example: > > 4.33 x .615385 = 2.66461705 > > ..00461705 is less then half of .01 so it goes down. Your calculator is > incorrect according to math teachers. > > As a side, if you had a dollar in pennies and wanted to give it in equal > parts to three people what would you give each one? > Where would the extra penny go? How would you do that in a program? > > These are the problems of money not being just numbers but having only 2 > places ( here in the US ) numbers must be forced into pennies someone gets > more someone gets less, all fractions of a penny must go somewhere. > > George. > > From: "Kennedy, Randy"<RKennedy@scottsdaleaz.gov> > To: ids@iiug.org > Date: 09/22/2011 12:43 PM > Subject: Rounding issues [25010] > Sent by: ids-bounces@iiug.org > > IDS: 11.10.FC3 > OS: RHEL 5.7 > > I am working with money and doing calculations of small dollar amounts x > multiple small percentages and having rounding issues getting the sum of > the > parts to equal the original total. > I have looked at the manual for the round function and it appears that this > is > normal functionality, but doesn't match, in my opinion, on how rounding > should > be performed. > > Example: > I need to split $4.33 into 3 component parts and I have 6 digits of > precision > for my split percentages. > 4.33 x .615385 > 4.33 x .307692 > 4.33 x .076923 > > ------------ > > 1.0 > > Current server calculations: > 4.33 x .615385 = 2.66 > 4.33 x .307692 = 1.33 > 4.33 x .076923 = .33 > > --------- > > 4.32 so I am a penny off > > In doing this on a calculator: > 4.33 x .615385 = 2.66461705 which rounds to 2.67 when calculator set to > round,2 and what I expected. > > On the server: > ROUND(4.33 x .615385,2) or (4.33 x .615385)::money(10,2), which is what I > am > doing, gives the value as 2.66 instead of the expected 2.67 > > Doesn't make a whole lot of sense to me as round,3 gives 2.665 which is > rounding the 4 to a 5 in the thousandths place based on the 6 in the ten > thousandths place. > Why doesn't it round the same way for the hundredths place? > > In doing more investigation, I found that if I do round((4.33 x > ...615385),3)::money(10,2) gives me 2.67 > Not sure I have much faith in Informix's calculations now... > If my answer is 2.664444445, I would still expect this to round to 2.67, > but > even my 'cheat' of round,3::money(10,2) would return 2.66 > > Is this a bug? Is it really supposed to function like that? With the > functionality as currently happening, I almost always have a penny problem > when I have to split small amounts between multiple accounts. > > The manual shows: > Expression: > ROUND (24,536.8746, -2) = 24,500.00 > ROUND (24,536.8746, 0) = 24,537.00 > ROUND (24,536.8746, 2) = 24,536.87 > > I understand the first 2, but the last one, again in my opinion, should be > 24,536.88. > > TIA, > Randy > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 / V11.70 IBM Information Management Informix Technical Professional v3
Randy: <<Lecture>> The way I learned rounding Informix's rounding is correct. When I was in school, I was taught that anything 0.50000000000000000000000000000000 or greater rounds up to 1.0 and anything 0.499999999999999999999999999 or less rounds down to 0.0. That is how Informix works. <</Lecture> ;-} Honestly, when you are working with partial pennies, there IS NO ROUNDING algorithm that will always give a perfect result every time, but you can improve things somewhat. You can get some more accurate calculations involving small fractions if you cast the MONEY columns, which are defined to two decimal places by default, to higher precision, say DECIMAL(32,16), before performing the calculation and then round the result to assign it back to the MONEY type variable. That will eliminate some of these apparent rounding problems. Where 0.002 + 0.004 + 0.003 are being added but the result is 0.00 instead of 0.01 as one would expect because the precision of the third decimal place is being rounded off before adding the values together, this will fix the problem. For other situations, where the partial results are being truncated to fit into a MONEY type for storage and so don't add up as expected when you add up the partials later, there's nothing you can do about it except to carry more decimal places in these partial results. If you store with sufficient precision (ie the product of the number of significant digits in the operands) you WILL get accurate results (though accurate may still not round to the result you want later - read on). If you can't save that level of precision, then your only recourse is to use the old IEEE float/double rounding trick of adding 0.0005 before rounding so that 0.4445 rounds to 0.45 instead of to 0.44 when you are checking the summation. Of course doing this will result in you having a penny too much almost as often as you are getting a penny to little now. Increased precision is the only 'real' answer. If you go for higher precision, and your reports have to add up, you still may have to deal with where to put the missing penny (you could get 10 partial results each of which has $0.001 so while there should be an extra penny somewhere for the total to match the sum, no rounding algorithm will display it. You will have to decide where to report the penny. I worked on a system once, where the rule was to just add it to the last line in the report. Another system had me adding a "balancing entry" for the penny after the last detail line. Other's didn't care. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 22, 2011 at 1:43 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov>wrote: > IDS: 11.10.FC3 > OS: RHEL 5.7 > > I am working with money and doing calculations of small dollar amounts x > multiple small percentages and having rounding issues getting the sum of > the > parts to equal the original total. > I have looked at the manual for the round function and it appears that this > is > normal functionality, but doesn't match, in my opinion, on how rounding > should > be performed. > > Example: > I need to split $4.33 into 3 component parts and I have 6 digits of > precision > for my split percentages. > 4.33 x .615385 > 4.33 x .307692 > 4.33 x .076923 > > ------------ > > 1.0 > > Current server calculations: > 4.33 x .615385 = 2.66 > 4.33 x .307692 = 1.33 > 4.33 x .076923 = .33 > > --------- > > 4.32 so I am a penny off > > In doing this on a calculator: > 4.33 x .615385 = 2.66461705 which rounds to 2.67 when calculator set to > round,2 and what I expected. > > On the server: > ROUND(4.33 x .615385,2) or (4.33 x .615385)::money(10,2), which is what I > am > doing, gives the value as 2.66 instead of the expected 2.67 > > Doesn't make a whole lot of sense to me as round,3 gives 2.665 which is > rounding the 4 to a 5 in the thousandths place based on the 6 in the ten > thousandths place. > Why doesn't it round the same way for the hundredths place? > > In doing more investigation, I found that if I do round((4.33 x > ..615385),3)::money(10,2) gives me 2.67 > Not sure I have much faith in Informix's calculations now... > If my answer is 2.664444445, I would still expect this to round to 2.67, > but > even my 'cheat' of round,3::money(10,2) would return 2.66 > > Is this a bug? Is it really supposed to function like that? With the > functionality as currently happening, I almost always have a penny problem > when I have to split small amounts between multiple accounts. > > The manual shows: > Expression: > ROUND (24,536.8746, -2) = 24,500.00 > ROUND (24,536.8746, 0) = 24,537.00 > ROUND (24,536.8746, 2) = 24,536.87 > > I understand the first 2, but the last one, again in my opinion, should be > 24,536.88. > > TIA, > Randy > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e8cf65e667104ad8cbcff
Based on several responses, it looks like I learned rounding differently than most. I learned it as if the digit next to the one I am rounding to is a 4, keep look right to see if it would round to 5, which would then round the one of concern up 1. Guess I didn't have a good teacher:) I.E. rounding to hundredths: .4844 = .48 .4845 = .49 .4844444444444445 still = .49 and the answer our printing calculators show when set to round,2 so it seemed to support the way I learned it. .48399999 = .48 Now that I understand how Informix will deal with it, I need to have my calculations work they way I learned it. Not because I think I am smarter than Informix, but because in dealing with money and it penny restriction, having it round like I would round would most often eliminate a penny problem that has to be addressed separately. I still have to have the penny solution in place to resolve the basic issue of $1 split 3 ways evenly. I know that won't work out no matter how it is rounded and a choice will have to be made on where to 'plug' the extra penny to ensure the full amount gets accounted for. Another issue I am facing is various tools looking at the same data, they all need to match. For example, the problem that brought this to my attention is that I had 1 place that took the money, did the split calculations, accounted for the penny, and reported results. No problem with penny being plugged as it was being plugged for each split and always equaled the amount being split and it would all work out in the end when all payments had been made/balances reduced to 0. The splits always equaled up to the amount being split Now, I have a new situation with needing to further split an already split figure for an entirely different reason and it is getting the amount to split solely from a raw calculation, not the entire previous split process so it isn't aware of any plugs that had to occur to balance. Now when I sum the new split and it gets compared with the split from the other mechanism, I was ending up a few cents off. Thank you all for your responses. Thanks, Randy -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, September 22, 2011 12:31 PM To: ids@iiug.org Subject: Re: Rounding issues [25016] Randy: <<Lecture>> The way I learned rounding Informix's rounding is correct. When I was in school, I was taught that anything 0.50000000000000000000000000000000 or greater rounds up to 1.0 and anything 0.499999999999999999999999999 or less rounds down to 0.0. That is how Informix works. <</Lecture> ;-} Honestly, when you are working with partial pennies, there IS NO ROUNDING algorithm that will always give a perfect result every time, but you can improve things somewhat. You can get some more accurate calculations involving small fractions if you cast the MONEY columns, which are defined to two decimal places by default, to higher precision, say DECIMAL(32,16), before performing the calculation and then round the result to assign it back to the MONEY type variable. That will eliminate some of these apparent rounding problems. Where 0.002 + 0.004 + 0.003 are being added but the result is 0.00 instead of 0.01 as one would expect because the precision of the third decimal place is being rounded off before adding the values together, this will fix the problem. For other situations, where the partial results are being truncated to fit into a MONEY type for storage and so don't add up as expected when you add up the partials later, there's nothing you can do about it except to carry more decimal places in these partial results. If you store with sufficient precision (ie the product of the number of significant digits in the operands) you WILL get accurate results (though accurate may still not round to the result you want later - read on). If you can't save that level of precision, then your only recourse is to use the old IEEE float/double rounding trick of adding 0.0005 before rounding so that 0.4445 rounds to 0.45 instead of to 0.44 when you are checking the summation. Of course doing this will result in you having a penny too much almost as often as you are getting a penny to little now. Increased precision is the only 'real' answer. If you go for higher precision, and your reports have to add up, you still may have to deal with where to put the missing penny (you could get 10 partial results each of which has $0.001 so while there should be an extra penny somewhere for the total to match the sum, no rounding algorithm will display it. You will have to decide where to report the penny. I worked on a system once, where the rule was to just add it to the last line in the report. Another system had me adding a "balancing entry" for the penny after the last detail line. Other's didn't care. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 22, 2011 at 1:43 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov>wrote: > IDS: 11.10.FC3 > OS: RHEL 5.7 > > I am working with money and doing calculations of small dollar amounts x > multiple small percentages and having rounding issues getting the sum of > the > parts to equal the original total. > I have looked at the manual for the round function and it appears that this > is > normal functionality, but doesn't match, in my opinion, on how rounding > should > be performed. > > Example: > I need to split $4.33 into 3 component parts and I have 6 digits of > precision > for my split percentages. > 4.33 x .615385 > 4.33 x .307692 > 4.33 x .076923 > > ------------ > > 1.0 > > Current server calculations: > 4.33 x .615385 = 2.66 > 4.33 x .307692 = 1.33 > 4.33 x .076923 = .33 > > --------- > > 4.32 so I am a penny off > > In doing this on a calculator: > 4.33 x .615385 = 2.66461705 which rounds to 2.67 when calculator set to > round,2 and what I expected. > > On the server: > ROUND(4.33 x .615385,2) or (4.33 x .615385)::money(10,2), which is what I > am > doing, gives the value as 2.66 instead of the expected 2.67 > > Doesn't make a whole lot of sense to me as round,3 gives 2.665 which is > rounding the 4 to a 5 in the thousandths place based on the 6 in the ten > thousandths place. > Why doesn't it round the same way for the hundredths place? > > In doing more investigation, I found that if I do round((4.33 x > ..615385),3)::money(10,2) gives me 2.67 > Not sure I have much faith in Informix's calculations now... > If my answer is 2.664444445, I would still expect this to round to 2.67, > but > even my 'cheat' of round,3::money(10,2) would return 2.66 > > Is this a bug? I
You will have to write your own rounding function in "C" or Java and install it as a UDR to be used in all of your calculations so it works consistently. As you say, still doesn't solve the missing penny problem when you don't know any longer, at the point of the calculation, that it has gone walkabout earlier in the process <sigh>. One algorithm I played with years ago that showed promise to solve the problem during the calculations is to take the remainder from the sum of the rounded values, divide by the number of values that went into the sum (so calculate the average discrepancy), and add that average to each of the pre-rounded values and round again. That seems to work until you apply it to the case when you divide 1 unit evenly into n pieces. In that case you end up with <n-1> pennies too many which is worse. Like I said, no algorithm is going to solve this one for you. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 22, 2011 at 4:03 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov>wrote: > Based on several responses, it looks like I learned rounding differently > than > most. I learned it as if the digit next to the one I am rounding to is a 4, > keep look right to see if it would round to 5, which would then round the > one > of concern up 1. Guess I didn't have a good teacher:) > I.E. rounding to hundredths: > ..4844 = .48 > ..4845 = .49 > ..4844444444444445 still = .49 and the answer our printing calculators show > when set to round,2 so it seemed to support the way I learned it. > ..48399999 = .48 > > Now that I understand how Informix will deal with it, I need to have my > calculations work they way I learned it. > Not because I think I am smarter than Informix, but because in dealing with > money and it penny restriction, having it round like I would round would > most > often eliminate a penny problem that has to be addressed separately. > > I still have to have the penny solution in place to resolve the basic issue > of > $1 split 3 ways evenly. I know that won't work out no matter how it is > rounded > and a choice will have to be made on where to 'plug' the extra penny to > ensure > the full amount gets accounted for. > > Another issue I am facing is various tools looking at the same data, they > all > need to match. > > For example, the problem that brought this to my attention is that I had 1 > place that took the money, did the split calculations, accounted for the > penny, and reported results. No problem with penny being plugged as it was > being plugged for each split and always equaled the amount being split and > it > would all work out in the end when all payments had been made/balances > reduced > to 0. The splits always equaled up to the amount being split > > Now, I have a new situation with needing to further split an already split > figure for an entirely different reason and it is getting the amount to > split > solely from a raw calculation, not the entire previous split process so it > isn't aware of any plugs that had to occur to balance. Now when I sum the > new > split and it gets compared with the split from the other mechanism, I was > ending up a few cents off. > > Thank you all for your responses. > > Thanks, > Randy > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Thursday, September 22, 2011 12:31 PM > To: ids@iiug.org > Subject: Re: Rounding issues [25016] > > Randy: > > <<Lecture>> > The way I learned rounding Informix's rounding is correct. When I was in > school, I was taught that anything 0.50000000000000000000000000000000 or > greater rounds up to 1.0 and anything 0.499999999999999999999999999 or less > rounds down to 0.0. That is how Informix works. > <</Lecture> > ;-} > > Honestly, when you are working with partial pennies, there IS NO ROUNDING > algorithm that will always give a perfect result every time, but you can > improve things somewhat. > > You can get some more accurate calculations involving small fractions if > you > cast the MONEY columns, which are defined to two decimal places by default, > to higher precision, say DECIMAL(32,16), before performing the calculation > and then round the result to assign it back to the MONEY type variable. > That will eliminate some of these apparent rounding problems. Where 0.002 + > 0.004 + 0.003 are being added but the result is 0.00 instead of 0.01 as one > would expect because the precision of the third decimal place is being > rounded off before adding the values together, this will fix the problem. > For other situations, where the partial results are being truncated to fit > into a MONEY type for storage and so don't add up as expected when you add > up the partials later, there's nothing you can do about it except to carry > more decimal places in these partial results. If you store with sufficient > precision (ie the product of the number of significant digits in the > operands) you WILL get accurate results (though accurate may still not > round > to the result you want later - read on). > > If you can't save that level of precision, then your only recourse is to > use > the old IEEE float/double rounding trick of adding 0.0005 before rounding > so > that 0.4445 rounds to 0.45 instead of to 0.44 when you are checking the > summation. Of course doing this will result in you having a penny too much > almost as often as you are getting a penny to little now. Increased > precision is the only 'real' answer. > > If you go for higher precision, and your reports have to add up, you still > may have to deal with where to put the missing penny (you could get 10 > partial results each of which has $0.001 so while there should be an extra > penny somewhere for the total to match the sum, no rounding algorithm will > display it. You will have to decide where to report the penny. I worked on > a system once, where the rule was to just add it to the last line in the > report. Another system had me adding a "balancing entry" for the penny > after the last detail line. Other's didn't care. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or > by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Thu, Sep 22, 20
Whats the problem? Put the extra penny into your account!! Thats what programmers at banks did years ago....until they were caught. On 9/22/2011 4:03 PM, Kennedy, Randy wrote: > Based on several responses, it looks like I learned rounding differently than > most. I learned it as if the digit next to the one I am rounding to is a 4, > keep look right to see if it would round to 5, which would then round the one > of concern up 1. Guess I didn't have a good teacher:) > I.E. rounding to hundredths: > ..4844 = .48 > ..4845 = .49 > ..4844444444444445 still = .49 and the answer our printing calculators show > when set to round,2 so it seemed to support the way I learned it. > ..48399999 = .48 > > Now that I understand how Informix will deal with it, I need to have my > calculations work they way I learned it. > Not because I think I am smarter than Informix, but because in dealing with > money and it penny restriction, having it round like I would round would most > often eliminate a penny problem that has to be addressed separately. > > I still have to have the penny solution in place to resolve the basic issue of > $1 split 3 ways evenly. I know that won't work out no matter how it is rounded > and a choice will have to be made on where to 'plug' the extra penny to ensure > the full amount gets accounted for. > > Another issue I am facing is various tools looking at the same data, they all > need to match. > > For example, the problem that brought this to my attention is that I had 1 > place that took the money, did the split calculations, accounted for the > penny, and reported results. No problem with penny being plugged as it was > being plugged for each split and always equaled the amount being split and it > would all work out in the end when all payments had been made/balances reduced > to 0. The splits always equaled up to the amount being split > > Now, I have a new situation with needing to further split an already split > figure for an entirely different reason and it is getting the amount to split > solely from a raw calculation, not the entire previous split process so it > isn't aware of any plugs that had to occur to balance. Now when I sum the new > split and it gets compared with the split from the other mechanism, I was > ending up a few cents off. > > Thank you all for your responses. > > Thanks, > Randy > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Thursday, September 22, 2011 12:31 PM > To: ids@iiug.org > Subject: Re: Rounding issues [25016] > > Randy: > > <<Lecture>> > The way I learned rounding Informix's rounding is correct. When I was in > school, I was taught that anything 0.50000000000000000000000000000000 or > greater rounds up to 1.0 and anything 0.499999999999999999999999999 or less > rounds down to 0.0. That is how Informix works. > <</Lecture> > ;-} > > Honestly, when you are working with partial pennies, there IS NO ROUNDING > algorithm that will always give a perfect result every time, but you can > improve things somewhat. > > You can get some more accurate calculations involving small fractions if you > cast the MONEY columns, which are defined to two decimal places by default, > to higher precision, say DECIMAL(32,16), before performing the calculation > and then round the result to assign it back to the MONEY type variable. > That will eliminate some of these apparent rounding problems. Where 0.002 + > 0.004 + 0.003 are being added but the result is 0.00 instead of 0.01 as one > would expect because the precision of the third decimal place is being > rounded off before adding the values together, this will fix the problem. > For other situations, where the partial results are being truncated to fit > into a MONEY type for storage and so don't add up as expected when you add > up the partials later, there's nothing you can do about it except to carry > more decimal places in these partial results. If you store with sufficient > precision (ie the product of the number of significant digits in the > operands) you WILL get accurate results (though accurate may still not round > to the result you want later - read on). > > If you can't save that level of precision, then your only recourse is to use > the old IEEE float/double rounding trick of adding 0.0005 before rounding so > that 0.4445 rounds to 0.45 instead of to 0.44 when you are checking the > summation. Of course doing this will result in you having a penny too much > almost as often as you are getting a penny to little now. Increased > precision is the only 'real' answer. > > If you go for higher precision, and your reports have to add up, you still > may have to deal with where to put the missing penny (you could get 10 > partial results each of which has $0.001 so while there should be an extra > penny somewhere for the total to match the sum, no rounding algorithm will > display it. You will have to decide where to report the penny. I worked on > a system once, where the rule was to just add it to the last line in the > report. Another system had me adding a "balancing entry" for the penny > after the last detail line. Other's didn't care. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Thu, Sep 22, 2011 at 1:43 PM, Kennedy, Randy > <RKennedy@scottsdaleaz.gov>wrote: > >> IDS: 11.10.FC3 >> OS: RHEL 5.7 >> >> I am working with money and doing calculations of small dollar amounts x >> multiple small percentages and having rounding issues getting the sum of >> the >> parts to equal the original total. >> I have looked at the manual for the round function and it appears that this >> is >> normal functionality, but doesn't match, in my opinion, on how rounding >> should >> be performed. >> >> Example: >> I need to split $4.33 into 3 component parts and I have 6 digits of >> precision >> for my split percentages. >> 4.33 x .615385 >> 4.33 x .307692 >> 4.33 x .076923 >> >> ------------ >> >> 1.0 >> >> Current server calculations: >> 4.33 x .615385 = 2.66 >> 4.33 x .307692 = 1.33 >> 4.33 x .076923 = .33 >> >> --------- >> >> 4.32 so I am a penny off >> >> In doing this on a calculator: >> 4.33 x .615385 = 2.66461705 which rounds to 2.67 when calculator set to >> round,2 and what I expected. >> >> On the server: >> ROUND(4.33 x .615385,2) or (4.33 x .615385)::money(10,2), which is what I >> am >> doing, gives the value as 2.66 instead of the expected 2.67 >> >> Doesn't make a whole lot of sense to me as round,3 gives 2.665 which is >> rounding the 4 to a 5 in the thousandths place based on the 6 in th