Chapter 19: Making Your Formulas Error-Free
19
The first formula, as expected, returns 9. The second formula, however, returns –9. Squaring
a number always produces a positive result, so how is it that Excel can return the –9 result?
In the first formula, the minus sign is a negation operator and has the highest precedence.
However, in the second formula, the minus sign is a subtraction operator, which has a lower
precedence than the exponentiation operator. Therefore, the value 3 is squared, and then the
result is subtracted from 0 (zero), which produces a negative result.
Using parentheses, as shown in the following formula, causes Excel to interpret the opera-
tor as a minus sign rather than a negation operator. This formula returns –9:
=-(3^2)Formulas are not calculated
If you use custom worksheet functions written in VBA, you may find that the formulas that
use these functions fail to be recalculated and may display incorrect results. For example,
assume that you wrote a VBA function that returns the number format of a referenced cell.
If you change the number format, the function will continue to display the previous num-
ber format. That’s because changing a number format doesn’t trigger a recalculation.
To force a single formula to be recalculated, select the cell, press F2, and then press Enter.
To force a recalculation of all formulas, press Ctrl+Alt+F9.
Problems with decimal precision
By their nature, computers don’t have infinite precision. Excel stores numbers in binary
format by using 8 bytes, which can handle numbers with 15-digit accuracy. Some numbers
can’t be expressed precisely by using 8 bytes, so the number is stored as an approximation.
To demonstrate how this lack of precision may cause problems, enter the following formula
into cell A1:
=(5.1-5.2)+1The result should be 0.9. However, if you format the cell to display 15 decimal places, you
discover that Excel calculates the formula with a result of 0.899999999999999. This
result occurs because the operation in parentheses is performed first, and this intermediate
result stores in binary format by using an approximation. The formula then adds 1 to this
value, and the approximation error is propagated to the final result.
In many cases, this type of error doesn’t present a problem. However, if you need to test
the result of that formula by using a logical operator, it may present a problem. For exam-
ple, the following formula (which assumes that the previous formula is in cell A1) returns
FALSE:
=A1=.9