Incorrect calculation!

Topic

New Reply

Why is it that when I get access to perform a calculation in a query such as Expres1:=[value1]/100
I get an answer such as 0.850000023841858 instead of just .85? This is having an effect down the line and resulting in mathematical errors!

Reply | Quote

Replies

You haven’t told Access you want to round the number, so it is returning the full value as a double. Look up Round in on-line help.

Reply | Quote

For the same reason that you don’t get .33 when you divide 1 by 3 in decimal arithmetic. When you convert [value1] and 100 to binary floating point numbers and thendivide them, the answer does not come out to excactly .85 because of rounding errors, it comes out to 0.850000023841858. You can fix this by changing your formula to:

Expres1:=Round([value1]/100,2)

In the above, the 2 is the number of decimal place to which you want to round, in this case 2 to give you .85.

Reply | Quote

Does this mean that if I want 14 point precision and I divide 85 by 100 that I must settle for a wrong value?
i.e. 0.850000023841858 becomes 0.85000002384186 instead of the actual value of .85000000000000

Reply | Quote

The value is not wrong, it is correct in the number system used by computers. In decimal, 2/3 to 14 decimals is: .66666666666667. Is that incorrect because the 6 in the 15th place caused the last digit to round up to 7? But, yes, if you want 14 digits, you will have to settle for the rounding error you get when you do deicmal arithmetic in binary floating point.

Reply | Quote

Define “wrong”!

Computers “like” to do arithmetic using the base 2 (i.e. binary) system. That’s no big deal for integers; what is an integer in one number system is an integer in any other number system. It DOES matter for floating point (i.e. fractions).

What is a repeating decimal in one base (e.g. 1/3 = 0.333333…. in base 10) is not necessarily a repeating decimal in another base. 1/3 in base 10 is 1/10 in base 3, which is 0.1 in base 3. 1/10 (or .1) in base 10 is 1/1010 in base 2, which is the repeating binary fraction .00110011….

That’s a long-winded way of saying that you’ll get rounding errors in floating point arithmetic on computers even where you might think that a particular fraction can be represented exactly.

There are often ways around that at a cost of efficiency or number range. For instance, if all you care about is 2 decimal places, you can multiply everything by 100 and work in integers (e.g. store cents rather than dollars). That’s basically how the currency type works except that it scales by 10000 rather than 100 to allow 4 decimal places.

PS Hey! I found somebody else with my initials!

Reply | Quote