Rounding

[JVanB]

I have a query which multiplies a rate (double) by a number (double) to
create a result (double). Using the ROUND function it rounded .455 to
..47, and .755 to .75. How do I fix this?

 

[John Vinson]

I have a query which multiplies a rate (double) by a number (double) to
create a result (double). Using the ROUND function it rounded .455 to
.47, and .755 to .75. How do I fix this?

I'm skeptical. Access uses "Banker's Rounding" - a number with 5 in
the place after the round point will round to the nearest even number;
that is, .455 should round to .46 (not .47), and .445 should round
down to .44. THIS IS NOT AN ERROR; if you always "round up" from 5,
the average of the rounded numbers will be larger than the average of
the source. Is this what's happening?

John W. Vinson[MVP]

 

[JVanB]

I'm skeptical. Access uses "Banker's Rounding" - a number with 5 in
the place after the round point will round to the nearest even number;
that is, .455 should round to .46 (not .47), and .445 should round
down to .44. THIS IS NOT AN ERROR; if you always "round up" from 5,
the average of the rounded numbers will be larger than the average of
the source. Is this what's happening?

John W. Vinson[MVP]

2279.755 rounds to 2279.76, and 924.225 rounds to 924.22, as you
predicted from the above. However, 2163.525 rounds to 2163.53, while
185.445 (the product of 1.5 times 123.63) rounds to 185.46!

These numbers are in a query using Sum(Round([DayCharge],2)), where
DayCharge is [Elapsed Time] * [Rate]. Elapsed time is a decimal number
with the possible values of .25, .5 and .75.

These calculations are the basis of bills submitted by my client to
their users. I need to explain this thoroughly.

 

[Lynn Trapp]

PMFJI,
But since your fields, [Elapsed Time] and [Rate], are of datatype "double,"
I think you will find that the values are not exactly 1.5 and 123.63. It's a
factor of how floating point numbers work. If you change your formula to
Sum(Round([DayCharge],4)) you will likely see why the rounding is working
the way it is.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

I'm skeptical. Access uses "Banker's Rounding" - a number with 5 in
the place after the round point will round to the nearest even number;
that is, .455 should round to .46 (not .47), and .445 should round
down to .44. THIS IS NOT AN ERROR; if you always "round up" from 5,
the average of the rounded numbers will be larger than the average of
the source. Is this what's happening?

John W. Vinson[MVP]

2279.755 rounds to 2279.76, and 924.225 rounds to 924.22, as you predicted
from the above. However, 2163.525 rounds to 2163.53, while 185.445 (the
product of 1.5 times 123.63) rounds to 185.46!

These numbers are in a query using Sum(Round([DayCharge],2)), where
DayCharge is [Elapsed Time] * [Rate]. Elapsed time is a decimal number
with the possible values of .25, .5 and .75.

These calculations are the basis of bills submitted by my client to their
users. I need to explain this thoroughly.

 

[JVanB]

PMFJI,
But since your fields, [Elapsed Time] and [Rate], are of datatype "double,"
I think you will find that the values are not exactly 1.5 and 123.63. It's a
factor of how floating point numbers work. If you change your formula to
Sum(Round([DayCharge],4)) you will likely see why the rounding is working
the way it is.

No rounding is applied until I save the data in a table prior to
printing a report. I suspect you may well be correct that the problem is
in the way the datatype "double" stores the data. What I need however is
a work around. Will changing the data type to "currency" do me any good?