Strange Addition-Subtraction Result

[ejc00]

When adding (or subtracting) two numbers in Microsoft Access or Excel, both programs give a strange result. This does not occur for all numbers, just certain ones. For instance, when I add A and B from the following two sets of numbers, I get C as the result:

A 0.268900000000000000000
B -0.260000000000000000000
C 0.008899999999999960000

A 0.269000000000000000000
B -0.260000000000000000000
C 0.009000000000000010000

Typically this is insignificant, however, we are using these results for standard deviation and correlation calculations, and this is having a effect on the calculations. Does anyone know why the Office products do this? Is there any fix for it?

Thanks,
Evan

 

[cmmrfrds]

The data types you are using are probably double or floating point, which always are approximates. Try the decimal data type, here is a description from access help.

Decimal variables are stored as 96-bit (12-byte) signed integers scaled by a variable power of 10. The power of 10 scaling factor specifies the number of digits to the right of the decimal point, and ranges from 0 to 28. With a scale of 0 (no decimal places), the largest possible value is +/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is +/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001.

Note At this time the Decimal data type can only be used within a Variant, that is, you cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.

 

[ejc00]

Thanks for the info... darn Microsoft Office!