Rounding Issues - HELP!

[Cygnus]

Sorry in advance for the lack of formatting in this posting.

Data:

(column headers)
Net Sales | Royalty Rate | Total Royalty
(data)
4.31 | 50.00% | 2.15
19.35 | 50.00% | 9.68

What gives?

Here is the query that runs this calculation:

UPDATE
[WT_Royalties-detail]
SET
[WT_Royalties-detail].[Total Royalty] = Round([Net Sales]*[Royalty
Rate],2)
WHERE
((([WT_Royalties-detail].Ref)="LSI"));

So why is the first row rounding down while the second row is rounding
up?

Note: Before I added the Round() function, the rows returned 2.155 and
9.675 respectively, if that helps.

Thanks!

 

[Van T. Dinh]

I guess conversion & truncation errors come into play here.

Mathematically, you are correct but the number as represented in binary and
only very few (decimal) fractional number can be exactly represented in
binary (namely the negative powers of 2 up to the number of binary digits
allowed by the data type).

Thus while mathematically, you are correct but the number may be represent
in binary as:

2.1549999999 and
9.6750000001

in this case, the results will be rounded to 2.15 and 9.68 respectively.

If I need to ensure that the exactly decimal digit 5 is rounded exactly one
way, e.g. always up, I add (or substract) a tiny MarginOfErrors in my
calculation like:

UPDATE [WT_Royalties-detail]
SET [WT_Royalties-detail].[Total Royalty] =
Round([Net Sales]*[Royalty Rate] + 0.000001,2)
WHERE ((([WT_Royalties-detail].Ref)="LSI"));

 

[Jim]

Expand the 2.155 to 4 decimal places. It should be a number that is >=2.145
and <2.1550.

eg. 2.1549 is both 2.155 and 2.15.

 

[Jamie Collins]

while mathematically, you are correct but the number may be represent
in binary as:

2.1549999999 and
9.6750000001

in this case, the results will be rounded to 2.15 and 9.68 respectively.

That is the raison d'etre of Access/Jet's DECIMAL (scaled integer) type
i.e. exact and accurate. If the OP is using a floating point type, as
you seem to have assumed, then the workaround could be as simple as:
switch to using DECIMAL...

If I need to ensure that the exactly decimal digit 5 is rounded exactly one
way, e.g. always up, I add (or substract) a tiny MarginOfErrors in my
calculation like:

UPDATE [WT_Royalties-detail]
SET [WT_Royalties-detail].[Total Royalty] =
Round([Net Sales]*[Royalty Rate] + 0.000001,2)
WHERE ((([WT_Royalties-detail].Ref)="LSI"));

....Ah, I see you agree with me <g>: by adding a literal decimal value
(+ 0.000001) you are coercing the result of the calculation to the
DECIMAL type e.g.

SELECT TYPENAME(CDBL(2.1549999999) + 0.000001)

returns 'Decimal'.

Of course, the rounding is subsequently affected by the data type of
the destination column. In the quoted example, if [WT_Royalties-detail]
was of type DECIMAL then the value would be truncated asymmetrically
(no rounding).

Jamie.

--

 

[Cygnus]

Expand the 2.155 to 4 decimal places. It should be a number that is >=2.145
and <2.1550.

eg. 2.1549 is both 2.155 and 2.15.

Er.... no, I don't think so.

4.31 * .5 will never produce a result other than 2.155. If I expand it
to four decimal places, it will still be 2.155 but will just have a
trailing 0 as in 2.1550

If something other than 2.1550 is produced when calculating 4.31 * .5
(expanding to 4 decimal places), there is a problem with the
calculator... right?

 

[John Vinson]

4.31 * .5 will never produce a result other than 2.155. If I expand it
to four decimal places, it will still be 2.155 but will just have a
trailing 0 as in 2.1550

If something other than 2.1550 is produced when calculating 4.31 * .5
(expanding to 4 decimal places), there is a problem with the
calculator... right?

This depends on the datatype of the field.

Access (and all Windows programs) support various computer
representations of numbers. All have limitations of one sort or
another.

The default Number type in Access is Long Integer which doesn't allow
any decimals at all.

You can also use Float or Double; these are "floating point" numbers,
stored as a binary fraction and an exponent. Not all numbers can be
represented exactly by a binary fraction; just as 1/7 cannot be
represented exactly by a finite decimal number (0.142856142856...), so
0.1 cannot be represented exactly by a finite binary fraction. This
will lead to expressions being ALMOST right - I haven't tested this
example, but as a Float 4.31 * .5 might be 2.1550001 or 2.1549998.
Using Double will give you about 14 decimals of accuracy instead of
just over 7 but there will still be an approximation.

If you use a Currency or Decimal datatype you will NOT get this
approximation, but you will be limited to a fixed number of decimal
places - 4 for Currency, whatever you define for Decimal.

John W. Vinson[MVP]

 

[Van T. Dinh]

Thanks, Jamie.

I wasn't aware of the coercion to Decimal. The MarginOfError was the
vestige of my "Fortran 4 Watt V" days ...

--
Van T. Dinh
MVP (Access)

while mathematically, you are correct but the number may be represent
in binary as:

2.1549999999 and
9.6750000001

in this case, the results will be rounded to 2.15 and 9.68 respectively.

That is the raison d'etre of Access/Jet's DECIMAL (scaled integer) type
i.e. exact and accurate. If the OP is using a floating point type, as
you seem to have assumed, then the workaround could be as simple as:
switch to using DECIMAL...

If I need to ensure that the exactly decimal digit 5 is rounded exactly
one
way, e.g. always up, I add (or substract) a tiny MarginOfErrors in my
calculation like:

UPDATE [WT_Royalties-detail]
SET [WT_Royalties-detail].[Total Royalty] =
Round([Net Sales]*[Royalty Rate] + 0.000001,2)
WHERE ((([WT_Royalties-detail].Ref)="LSI"));

...Ah, I see you agree with me <g>: by adding a literal decimal value
(+ 0.000001) you are coercing the result of the calculation to the
DECIMAL type e.g.

SELECT TYPENAME(CDBL(2.1549999999) + 0.000001)

returns 'Decimal'.

Of course, the rounding is subsequently affected by the data type of
the destination column. In the quoted example, if [WT_Royalties-detail]
was of type DECIMAL then the value would be truncated asymmetrically
(no rounding).

Jamie.

 

[Jim]

Sub test()
Dim a As Single
a = 4.31
Debug.Print a * 0.5
Debug.Print a / 2
End Sub


Results:
' 2.15499997138977
' 2.155

Round(2.15499997138977,3) is 2.155
Round(2.15499997138977,2) is 2.15