Microsoft® Office and Floating Point Numbers
How Microsoft Access, Excel and VBA and some of their functions work with floating point numbers.
Last updated on 2024-05-15.
Floating Point Numbers
In digital computers, floating point numbers offer a method of representing very large and very small numbers that are beyond the capacity of the processor to represent with absolute accuracy.
There may be an error when computing with floating point numbers. That occurs when there are no exact binary equivalents for all the numbers or for the result of the calculation.
The addition, subtraction, multiplication and division of two floating point numbers result in a floating point number. Such calculations may well result in error.
To illustrate error in a floating point calculation, use the Immediate Window of the Visual Basic Editor in one of your Access databases or Excel spreadsheets and input the expression ?1.001-1 and then press Enter.
The 1.001-1 and 1 are treated as floating point numbers. On my computer the Immediate Window returns 9.9999999999989E-04. This is VBA's way of expressing the number that in scientific notation is written 9.9999999999989 × 10−4
Both 9.9999999999989E-04 and 9.9999999999989 × 10−4 repesent the number 0.00099999999999989.
0.00099999999999989 is not quite the correct 0.001. However, the difference is an error so tiny that it wouldn't be of consequence in most Access or Excel calculations.
The Single and Double precision data types in Access store numbers as floating point numbers. Therefore, in an Access query, form, or report, any expression drawing values from fields with these data types will be subject to error.
VBA, in both Access and Excel, supports the Single and Double precision data types for floating point numbers. So any VBA functions you write to work with floating point numbers are subject to error too.
Scaling Numbers
Scaling is the adjustment of a number so that it can be processed accurately by your computer.
The Access and VBA Currency data type scales numbers. Currency has its limits, however, because it will not store numbers beyond the fourth place of decimals.
So, invaluable as it may be for making accurate calculations, you can rely on error-free calculation only if the values used and the true results do not extend beyond this four decimal place limit.
In Access and VBA, you have the coercive CCur() function to perform scaling on any number (including floating point) you supply.
CCur() has the same four-decimal-place limit as the Currency data type.
VBA has the CDec() scaling function. It has limits but these are vastly wider than those applying to CCur().
To test the effect of applying scaling, try
?CDec(1.001)-1 and ?CCur(1.001)-1 in the Immediate window.
Both should yield the correct result.
?CCur(1.00001)-1 fails, as CCur() chops the 1.00001 off early at its four decimal place limit.
?CDec(1.00001)-1 does the business.
Note that you must scale the numbers before the calculation. Scale its result, as in ?CDec(1.001-1), and you will suffer floating point errors.
If you attempt a custom user-defined rounding function, to, say mirror Excel's ROUND(), you will be up against floating point errors.
Click here for our attempt at an Access-VBA, Excel-type ROUND().
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
Thanks, in anticipation.