INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

Is the calculation happening on the form, in a query, or in VBA?

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

RE: Plagued by rounding errors

Currency has 4 decimal places. I believe what you are experiencing is "bankers rounding". Since the '7' in 400.875 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

(OP)
First, kjv1611. The calculations are in a form, with the data being entered in a subform based on a query.

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

(OP)

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

I think you could use
=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

(OP)

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

RE: Plagued by rounding errors

I refuse to use 'currency'.
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





Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close