How Formulas Work

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.

Mathematical Operators

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.

Table 5.1. Excel Formula Operators

Operator	What It Does
+	Addition
-	Subtraction
*	Multiplication
/	Division
=	Equal to
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
<>	Not equal to
%	Percentage
^	Exponentiation

Figure 5.1 shows a very simple formula. The intent is pretty clear ”the formula asks Excel to add 20,000 to 1,000.

Figure 5.1. The formula in cell D4 contains a mathematical operator.

Order of Operations

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:

 =20,000+18,000*.050 

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:

1. All operations enclosed in parentheses

2. All exponential operations

3. All multiplication and division operations

4. 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

 (20,000+18,000)*.050 

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.

Figure 5.2. The formula in cell B9 groups the values and mathematical operator.

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.

Figure 5.3. Excel tells you when a formula has not been entered correctly.

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.

Figure 5.4. Excel tells you the formula is missing a parenthesis.