## Plagued by rounding errors

## Plagued by rounding errors

(OP)

I keep getting rounding errors when calculating amounts involving unit prices and quantities. All unit prices in their tables are formatted as Currency. These amounts below are in currency apart from the end ones, which are the true amounts.

Item Quantity Unit Price Discount at 5% Value Unformatted Value Product 1 1 92.11 4.61 (5%) 87.50 87.50 Product 2 2 93.41 4.67 (5%) 177.48 177.48 Product 3 1 92.11 0.00 (0%) 92.11 92.11 Total 357.09 357.09 Credit item Product 4 0.25 92.11 23.03 23.0275 Subtotal 334.06 334.0625 VAT at 20% 66.81 46.8125 Displayed Grand Total 400.88 400.875 Would expect 334.06 + 66.81 to be 400.87

I know rounding is well discussed but I don't seem able to get sensible results. Does a currency amount have 2 or 4 decimal places behind the scene, and how can I force the 'expected' answer of 400.87?

## RE: Plagued by rounding errors

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

## RE: Plagued by rounding errors

75 is odd, the '5' is rounded up. The number 400.865 would be rounded down to 400.86.Duane

Minnesota

Hook'D on Access

MS Access MVP 2001-2016

## RE: Plagued by rounding errors

Duane, yes have come across Banker's Rounding but it leaves the problem that when the data is transferred to an invoice the amount payable may be .01 different from the sum of the constituents. This would look strange since the customer sees both the constituents and their sum.

I wonder if it's possible to 'freeze' the constituent amounts as numbers with just two decimal places so the result looks correct, something like Clng(Amount *100)/100?

## RE: Plagued by rounding errors

You might find some solutions at Allen Browne's web page.

Duane

Minnesota

Hook'D on Access

MS Access MVP 2001-2016

## RE: Plagued by rounding errors

What looks to work is the Round function, so Round([Subtotal],2) + Round([VAT],2) gives the 'expected' answer 400.87 without a straight sum further rounding two numbers each with 4 decimal places.

I look at Allen Browne's articles frequently, thanks, and he does indeed talk a lot about rounding. I find it a bit alarming that having used rounding in calculations to get currency values for various items, Access then rounds the sum of these rather than just adding them.

I'm left wondering how far I have to use the Round fix as there are a large number of currency manipulations in my database.

## RE: Plagued by rounding errors

=Sum(Round([Numeric Field],2))I'm not an accountant so I have no idea what is expected.

Duane

Minnesota

Hook'D on Access

MS Access MVP 2001-2016

## RE: Plagued by rounding errors

Thanks for help Duane, the truth will out in time.

## RE: Plagued by rounding errors

Is it really a true 'data-type'?

Is it Microsofts attempt at 'cornering the market' / patenting a 'word'?

Could MS's 'currency' be naturally / accurately transferred to another database product?

I don't think so (and I've experienced enough Access 'currency' glitches to steer clear).

It's basically based on a 'business rule', that MS has defined.

Trouble is: no accountant will EVER state a rounding rule as 'global fact', it's whatever the current company has as a 'business rule'.

Stick to single / double data-types - they are fundamentally 'standard', and you can round up down or trim - as required.

Sometimes you have to stick to globally, historically accepted 'facts', and forget the 'clever' programming tricks.

D