Numerical calculations performed on either a computer or calculator are performed with only a finite number of digits; this introduces rounding errors. This isn't a problem with integer numbers . The number 1/3 is represented in decimal form as 0.33333333, but there needs to be an infinite number of threes following the decimal point. With four digits of precision, this is written as 0.3333. This introduces inaccuracies in the representation and the resulting calculations.
1/3 + 1/3 + 1/3 = 3/3 = 1 'The exact answer is 1 0.3333 + 0.3333 + 0.3333 = 0.9999 'The finite precision answer, off a bit
The simple macro in Listing 2 demonstrates this problem. The value 0.2 is repeatedly added to the variable num until the value is equal to 5. If infinite precision were used, or if the number 0.2 were exactly represented inside the computer, the loop would stop with the variable num containing the value 5. The variable never precisely equals the value 5, however, so the loop never stops. The value 5 is printed, but this is only because the Print statement rounds 4.9999999 to the value 5 when it prints the number.
Dim num As Single Do num = num + 0.2 If num > 4.5 Then Print num 'prints 4.6, 4.8, 5, 5.199999... Loop Until num = 5.0 Print num
Computers use complex rounding algorithms in an attempt to reduce the impact of finite precision-finite precision means that a finite number of digits and storage are used to represent a number. Although this helps, Listing 2 clearly demonstrates that the internal complex rounding algorithms do not solve the problem. When you compare two floating-point numbers to see if they are equal, it is safer to compare them to a range of values. The code in Listing 3 stops when the variable is greater than or equal to 5.
Dim num As Single Do num = num + 0.2 Loop Until num >= 5.0 Print num '5.199999
The code in Listing 3 works to some extent, but you probably want the loop to exit when the variable num is 4.9999999 rather than when it is 5.199999. You can do this by checking to see if two numbers are close rather than equal. The big question is, How close must two numbers be before they are considered equal? You can usually make a simple guess based on what you know about the problem. Single-precision variables can represent about eight digits of precision. Double-precision variables can represent about 16 digits of precision. Don't try to demand more precision from the variables than they support. The code in Listing 3 uses single-precision variables so you can expect roughly seven digits of precision. The code in Listing 4 prints the difference between 5 and num -notice that about six digits are correct.
Dim num As Single Do num = num + 0.2 Loop Until 4.99999 < num AND num < 5.00001 Print 5 - num '4.76837158203125E-07 = 0.000000476837158203125
The ABS function returns the absolute value of a number. You can use it to simplify the process of checking to see how close one number is to another.
If ABS(num - 5) < 0.00001 Then
Using ABS and subtraction indicates how close two numbers are to each other, but it may not be sufficient. For example, light travels at about 299,792,458 meters each second. This number contains nine digits. A single-precision number is only accurate to about seven digits. See Listing 5 .
Dim c1 As Single 'Scientists usually use the letter c to represent Dim c2 As Single 'the speed of light. c1 = 299792458 'Speed of light in meters per second to nine digits c2 = c1 + 16 'Add 16 to the speed of light If c1 = c2 Then 'These are equal because only the first seven Print "Equal" 'or eight digits are significant End If
The code in Listing 5 adds 16 to the speed of light, but this does not change the value. This is because only the first seven or eight digits are significant. The code in Listing 6 uses a number that is smaller in magnitude but uses the same number of digits. Adding 1 to the number would change a significant digit, but adding a smaller number still leaves the numbers equal.
Dim c1 As Single 'Scientists usually use the letter c to represent Dim c2 As Single 'the speed of light. c1 = 299.792458 'This is nine digits but it is not the speed of light c2 = c1 + .0000016 'Must add a smaller number for them to still be equal If c1 = c2 Then 'These are equal because only the first seven Print "Equal" 'or eight digits are significant End If
Floating-point numbers can have different magnitudes -magnitude refers to the size of the number-and it doesn't significantly affect the number of digits that are relevant. To check if two numbers are about the same value, large numbers can differ by a greater amount than small numbers. The greatest allowed difference is dependent upon the magnitude (size) of the numbers; a mathematician calls this the "relative error." See Listing 7 .
REM This uses nl as the primary number of interest REM n2 is compared to n1 in a relative way REM rel_diff is the desired relative difference REM rel_diff is assumed non-negative Function AreSameNumber(n1, n2, rel_diff) As Boolean AreSameNumber = False 'Assume that they are different If n1 <> 0 Then 'Cannot divide by n1 if it is zero If ABS((n1-n2)/n1) <= rel_diff Then 'Divide difference by n1 for relative AreSameNumber = True 'comparison. End If 'If n1, the number of interest, is ElseIf ABS(n2) <= rel_diff Then 'zero, then compare n2 for size. AreSameNumber = True End If End Function
The code in Listing 7 divides the difference of two numbers by one of the numbers. The code in Listing 8 checks numbers of different sizes to see if they are the same number.
Sub CheckSameNumber Dim s1 As Single Dim s2 As Single Print AreSameNumber(299792458, 299790000, le-5) 'True: five digits same Print AreSameNumber(299792458, 299700000, le-5) 'False: four digits same sl = 299792458 's1 assigned different value s2 = 299792448 'than s2 but same number. Print AreSameNumber(s1, s2, 0.0) 'True: Same number in single precision. Print AreSameNumber(299.792458, 299.790000, le-5)'True: five digits same Print AreSameNumber(2.99792458, 2.99700000, le-5)'False: four digits same End Sub
A large quantity of literature and research exists on the negative issues associated with floating-point numbers. A complete discussion is therefore well beyond the scope of this book. In general usage the problems typically aren't that troublesome , but when they arise they can be most perplexing if you aren't aware of the issues.