Problem with rounding error

[John]

I am using the Round worksheet function in a query. ExtendedPrice:
Round([UnitPrice]*{Quantity],2). It works ok except if the answer is XX.5 it
rounds down rather than up. Does anyone know why this is and can I change it
to round up rather than down.

Any help greatly appreciated

John

 

[John W. Vinson]

It works ok except if the answer is XX.5 it
rounds down rather than up. Does anyone know why this is and can I change it
to round up rather than down.

The Round function rounds up for fractional values greater than 0.5,
down for values less than 0.5 - and to the nearest EVEN number for 0.5
exactly. This is called "Banker's Rounding" and is preferred in many
cases, because it does not systematically increase the average value
of the numbers being rounded.

To always round up, use Round(<expression> + 0.0001, 2)

where the small number is smaller than the precision of your
expression.

John W. Vinson [MVP]

 

[John]

The Round function rounds up for fractional values greater than 0.5,
down for values less than 0.5 - and to the nearest EVEN number for 0.5
exactly. This is called "Banker's Rounding" and is preferred in many
cases, because it does not systematically increase the average value
of the numbers being rounded.

To always round up, use Round(<expression> + 0.0001, 2)

where the small number is smaller than the precision of your
expression.

John W. Vinson [MVP]

Thanks John, I always thought that rounding always rounded up at .5. A lot
of progams do as my problem is that my additions of prices sometimes don't
tally with the same calculations from other software programs. I'll try your
solution and see how it goes.

Regards
John Egan

 

[Ken Sheridan]

John:

There are more rounding algorithms than you can shake a stick at. For a
brief outline of the various types, and the rationale behind them, take a
look at the following links:


http://www.pldesignline.com/howto/showArticle.jhtml?articleID=175801189


http://www.diycalculator.com/sp-round.shtml


Ken Sheridan
Stafford, England