IDS Forum

Re: Rounding issues

Posted By: David Zinder
Date: Thursday, 22 September 2011, at 4:25 p.m.

In Response To: RE: Rounding issues (Kennedy, Randy)

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 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
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• Rounding issues
  Kennedy, Randy -- Thursday, 22 September 2011, at 1:43 p.m.
  • Re: Rounding issues
    George_Palmer@aotx.uscourts.gov -- Thursday, 22 September 2011, at 2:41 p.m.
    • Re: Rounding issues
      Alexandre Marini -- Thursday, 22 September 2011, at 2:54 p.m.
  • RE: Rounding issues
    Everett Mills -- Thursday, 22 September 2011, at 2:50 p.m.
  • Re: Rounding issues
    Art Kagel -- Thursday, 22 September 2011, at 3:31 p.m.
    • RE: Rounding issues
      Kennedy, Randy -- Thursday, 22 September 2011, at 4:03 p.m.
      • Re: Rounding issues
        Art Kagel -- Thursday, 22 September 2011, at 4:24 p.m.
      • Re: Rounding issues
        David Zinder -- Thursday, 22 September 2011, at 4:25 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.