A formula is a cell entry that calculates values to return a result. Each Excel formula must have three key elements: the equal sign (=) that signifies that the entry is a formula, the values or cell references to be calculated, and the mathematical operators, such as a plus sign (+) for addition or a minus sign (-) for subtraction.
All Excel formulas must begin with the equal sign. The equal sign tells Excel that the entry is a formula. If your formula begins without an equal sign, Excel treats it as a regular cell entry and doesn't perform the calculation.
If you begin a formula with a plus sign (+), Excel converts it to an equal sign (=). That's because Lotus 1-2-3, another spreadsheet program, uses the plus sign as its opening formula-entry character. Excel makes it easy for Lotus users to switch to Excel.
When you create a formula in Excel, you need to include an operator. All formulas must contain mathematical operators so that Excel knows what calculation to perform. Table 5.1 lists the arithmetic operators used in Excel.
What It Does
Less than or equal to
Greater than or equal to
Not equal to
Figure 5.1 shows a very simple formula. The intent is pretty clear?the formula asks Excel to add 20,000 to 1,000.
In a simple formula, such as the one shown in Figure 5.1, Excel is asked to perform only one calculation. However, formulas can often contain instructions to perform multiple calculations. If you were to talk out a more complicated formula, you might say something like this: "Add together the price of a car that costs $20,000 and a truck that costs $18,000 and then multiply the combined price by 5% sales tax to determine the sales tax due on the combined cost of the vehicles."
You might think that you'd enter the value for the formula this way:
However, if you typed that formula in a cell, the formula would be incorrect. The reason is that Excel uses something called operator precedence to perform calculations. Operator precedence determines the order in which calculations are performed. Calculations are performed from left to right in the following order:
All operations enclosed in parentheses
All exponential operations
All multiplication and division operations
All addition and subtraction operations
Many old-style math teachers explain the mathematical order of operations with the phrase My Dear Aunt Sally. The first letter of each word stands for its mathematical equivalent: multiply, divide, add, and then subtract.
The best way to force Excel to calculate your formulas correctly is to use parentheses. Group the values and operators that you want to calculate first in parentheses. For example, the formula
tells Excel to first add the numbers within the parentheses and then to calculate the sales tax percentage on the total. Figure 5.2 illustrates the order of operations with the formula =(B4+B7)*.0.05 in cell B9. Cell B4 contains 20,000 for the car purchase and cell B7 contains 18,000 for the truck purchase. The sales tax is 5%, which is .050. The parentheses tell Excel to perform the addition first and then multiply the sum by the tax percentage.
You can even nest parentheses within parentheses to further break down how you want Excel to calculate your formula. Just remember that each opening parenthesis must have a closing parenthesis. If your formula does not contain the required number of parentheses, Excel displays an error message similar to the one in Figure 5.3. Excel even attempts to place the missing parenthesis. If your formula doesn't contain the proper number of parentheses, Excel displays each parentheses set in a different color, to help you track your error.
When Excel detects a formula error because of too many or too few parentheses, it attempts to place the missing characters. If you let Excel correct your formula, check it over carefully. After all, Excel doesn't know what your calculation should accomplish.
Your best choice is to make the correction yourself. When you click No, Excel displays a message that provides further information on how to make the correction, as shown in Figure 5.4. Click OK and then make your correction to the formula.