… and when does 35 – 35 not equal zero?
In the first case the answer is when one “35” is on an Excel spreadsheet and the other 35 is in VBA. The question arose from the ongoing continuous beam discussion with metroxx, who had, quite reasonably, generated a series of output points by calculating the length of one step (35/30) then adding this value to the previous length in 30 stages. This mysteriously caused an error in the function for no obvious reason.
Stepping through the code I found that the error was occurring when the last output point was compared to the beam length and found to be greater. The mysterious part was that both on the worksheet (formatted to 15 decimal places), and in the VBA Locals window, both values displayed as being exactly 35. Also entering the formula = A62 – 35 returned exactly zero.
To investigate what was going on I wrote the short VBA function shown below:
Function Diff(A As Double, B As Double) As Double
Diff = A - B
End Function
The function simply subtracts B from A, but using this function with the incrementally generated “35” as A and the “exact” 35 as B returned a value of 7.11E-15, even though stepping through the routine both variables displayed as exactly 35 in the locals window.
The answer to the second question is the same as the first (as would be expected), but also in some cases this behaviour can be seen on the spreadsheet with no VBA involved. As stated above, the formula = A62 – 35 returns exactly zero, but the formula = (A62 – 35) returns 7.11E-15, the same as the UDF! The screenshots below show more detail of this behaviour.
In the spreadsheet in column B I have generated 30 increments with the formula =(B7+$C$4), where C4 is =C2/C3, i.e. 35/30.
The value in column C are generated with =$C$2*A8/$C$3, where C2 is the beam length, A8 is the increment number, and C3 is the number of increments (30). Column D contains the the formula =B8 – C8, Column E: =Diff(B8, CB), and column F: =(B8 – C8).
Floating point value check - click to view full size
Error in Column D result
It can be seen that Column D has returned a difference of zero in all cases, even though the VBA function in Column E and the formula in Column F show a difference of up to 1.42E-14.
These differences are of course caused in part by the fact that all values are stored as binary floating point values, that cannot represent all decimal or fractional values exactly. This is described in several Microsoft documents (e.g. Understanding Floating Point Precision), which all claim that Excel follows the IEEE Standard for Binary Floating-Point Arithmetic. Clearly this is not the whole story though, since the example given generates errors up to an order of magnitude greater than the maximum difference between any exact value and the nearest floating point value, and also generates different results depending on how worksheet formulas are entered.
I would be interested if anyone has any more background on exactly how these things are handled in Excel, but for practical purposes I think the lessons are:
- When comparing non-integer values be aware that values that display as exactly equal may be stored as different values.
- When comparing doubles in VBA either round the values to a suitable precision, or check that the difference is less than some small value, rather than exactly zero.
- Calculations that involve the difference between two nearly equal values may give incorrect results. Consider carrying out this type of calculation entirely in VBA (or if necessary in a different language offering higher precision calculations).
Example added 27th Dec 2011:
Another example of the effect of brackets:
- Cell B4, =B2 + B3, displays as exactly 1, because the 1.6E-15 would be the 16th and 17th significant figure, but only 15 significant figures are displayed.
- Cell B5, =B4 – 1, displays as exactly 0, the contents of B4 being treated as the displayed value.
- Cell B6, =(B2 + B3), displays as exactly 1, the same as B4, as would be expected.
- But Cell B7, =B6-1, displays as 0.000000000000001554, using the decimal equivalent of the underlying value stored in B6, rather than the displayed value.
- Cell B8, =(B6-B4), displays exactly 0, using the displayed values in both cells
- Cell B9, = B7-B5, displays 1.554E-15 (i.e. the same as B7), so
((1+x))-(1+x) <> ((1+x)-1)-(1+x-1) !
Newton Excel Bach, not (just) an Excel Blog 