IDS Forum

Re: Rounding issues

Posted By: Art Kagel
Date: Thursday, 22 September 2011, at 3:31 p.m.

In Response To: Rounding issues (Kennedy, Randy)

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

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.