Formula elements

A formula entered into a cell can consist of five elements:

• Operators: These include symbols such as + (for addition) and * (for multiplication).
• Cell references: These include named cells and ranges that can refer to cells in the current worksheet, cells in another worksheet in the same workbook, or even cells in a worksheet in another workbook.
• Values or text strings: Examples include 7.5 (a value) and “Year-End Results” (a string, enclosed in quotes).
• Worksheet functions and their arguments: These include functions such as SUM or AVERAGE and their arguments. Function arguments appear in parentheses and provide input for the function’s calculations.
• Parentheses: These control the order in which expressions within a formula are evaluated.

Entering a formula

When you type an equal sign into an empty cell, Excel assumes that you are entering a formula because a formula always begins with an equal sign. Excel’s accommodating nature also permits you to begin your formula with a minus sign or a plus sign. However, Excel always inserts the leading equal sign after you enter the formula.

As a concession to former Lotus 1-2-3 users, Excel also allows you to use an “at” symbol (@) to begin a formula that starts with a function. For example, Excel accepts either of the following formulas:

=SUM(A1:A200)
@SUM(A1:A200)

However, after you enter the second formula, Excel replaces the @ symbol with an equal sign.

If your formula uses a cell reference, you can enter the cell reference in one of two ways: enter it manually, or enter it by pointing to cells that are used in the formula. We discuss each method in the following sections.

Pasting names

As we discuss in Chapter 3, “Working with Names,” you can assign a name to a cell or range. If your formula uses named cells or ranges, you can type the name in place of the address or choose the name from a list and have Excel insert the name for you automatically.

To insert a name into a formula, position your cursor in the formula where you want the name entered and use one of these two methods:

• Press F3 to display the Paste Name dialog box. Select the name and click OK.
• Take advantage of the Formula AutoComplete feature. When you type a letter while constructing a formula, Excel displays a list of matching options. These options include functions and names.

Figure 2.1 shows Formula AutoComplete in use. In this case, SalesData is a defined range name. This name appears in the drop-down list, along with worksheet function names.

Spaces and line breaks

Normally, you enter a formula without using spaces. However, you can use spaces (and even line breaks) within your formulas. Doing so has no effect on the formula’s result but can make the formula easier to read. To enter a line break in a formula, press Alt+Enter. Figure 2.2 shows a formula that contains spaces (indentions) and line breaks.

Formula limits

A formula can consist of up to about 8,000 characters. In the unlikely event that you need to create a formula that exceeds this limit, you must break the formula into multiple formulas. You also can opt to create a custom function by using Visual Basic for Applications (VBA).

Sample formulas

If you follow the preceding instructions for entering formulas, you can create a variety of them. This section looks at some sample formulas.

• The following formula multiplies 150 × .01, returning 1.5. This formula uses only literal values, so it doesn’t seem very useful. However, it may be useful to show your work when you review your spreadsheet later:

=150*.01

• This formula adds the values in cells A1 and A2:

=A1+A2

• The next formula subtracts the value in the cell named Expenses from the value in the cell named Income:

=Income–Expenses

• The following formula uses the SUM function to add the values in the range A1:A12:

=SUM(A1:A12)

• The next formula compares cell A1 with cell C12 by using the = operator. If the values in the two cells are identical, the formula returns TRUE; otherwise, it returns FALSE:

=A1=C12

• This final formula subtracts the value in cell B3 from the value in cell B2 and then multiplies the result by the value in cell B4:

=(B2–B3)*B4

Editing formulas

If you make changes to your worksheet, you may need to edit formulas. Or if a formula returns one of the error values described later in this chapter, you might need to edit the formula to correct the error. You can edit your formulas just as you edit any other cell.

Here are several ways to get into cell edit mode:

• Double-click the cell. This enables you to edit the cell contents directly in the cell. This technique works only if the Double-click Allow Editing Directly in Cells check box is selected on the Advanced tab in the Excel Options dialog box.
• Press F2. This enables you to edit the cell contents directly in the cell. If the Double-Click Allow Editing Directly in Cells check box is not selected, the editing occurs in the Formula bar.
• Select the formula cell that you want to edit and then click in the Formula bar. This enables you to edit the cell contents in the Formula bar.

When you edit a formula, you can select multiple characters by dragging the mouse over them or by holding down Shift while you use the arrow keys. You can also press Home or End to select from the cursor position to the beginning or end of the current line of the formula.

Reference operators

Excel supports another class of operators known as reference operators; see Table 2.2. Reference operators work with cell references.

Sample formulas that use operators

These examples of formulas use various operators:

• The following formula joins (concatenates) the two literal text strings (each enclosed in quotes) to produce a new text string: Part-23A:

="Part-"&"23A"

• The next formula concatenates the contents of cell A1 with cell A2:

=A1&A2

• Usually, concatenation is used with text, but concatenation works with values as well. For example, if cell A1 contains 123, and cell A2 contains 456, the preceding formula would return the value 123456. Note that, technically, the result is a text string. However, if you use this string in a mathematical formula, Excel treats it as a number. Some Excel functions ignore this “number” because they are designed to ignore text.
• The following formula uses the exponentiation (^) operator to raise 6 to the third power to produce a result of 216:

=6^3

• A more useful form of the preceding formula uses a cell reference instead of the literal value. Note this example that raises the value in cell A1 to the third power:

=A1^3

• This formula returns the cube root of 216 (which is 6):

=216^(1/3)

• The next formula returns TRUE if the value in cell A1 is less than the value in cell A2. Otherwise, it returns FALSE:

=A1<A2

• Logical comparison operators also work with text. If A1 contains Alpha and A2 contains Gamma, the formula returns TRUE because Alpha comes before Gamma in alphabetical order.
• The following formula returns TRUE if the value in cell A1 is less than or equal to the value in cell A2. Otherwise, it returns FALSE:

=A1<=A2

• The next formula returns TRUE if the value in cell A1 does not equal the value in cell A2. Otherwise, it returns FALSE:

=A1<>A2

• Excel doesn’t have logical AND and OR operators. Rather, you use functions to specify these types of logical operators. For example, this formula returns TRUE if cell A1 contains either 100 or 1000:

=OR(A1=100,A1=1000)

• This last formula returns TRUE only if both cell A1 and cell A2 contain values less than 100:

=AND(A1<100,A2<100)

Operator precedence

You can (and should) use parentheses in your formulas to control the order in which the calculations occur. As an example, consider the following formula that uses references to named cells:

=Income–Expenses*TaxRate

The goal is to subtract expenses from income and then multiply the result by the tax rate. If you enter the preceding formula, though, you discover that Excel computes the wrong answer. The formula multiplies expenses by the tax rate and then subtracts the result from the income. In other words, Excel does not necessarily perform calculations from left to right (as you might expect).

The correct way to write this formula is

=(Income–Expenses)*TaxRate

To understand how this works, you need to be familiar with operator precedence—the set of rules that Excel uses to perform its calculations. Upcoming Table 2.3 lists Excel’s operator precedence. Operations are performed in the order listed in the table. For example, multiplication is performed before subtraction.

Use parentheses to override Excel’s built-in order of precedence. Returning to the previous example, the formula without parentheses is evaluated using Excel’s standard operator precedence. Because multiplication has a higher precedence, the Expenses cell multiplies by the TaxRate cell. Then this result is subtracted from Income—producing an incorrect calculation.

The correct formula uses parentheses to control the order of operations. Expressions within parentheses are always evaluated first. In this case, Expenses is subtracted from Income, and the result is multiplied by TaxRate.

Nested parentheses

You can also nest parentheses in formulas—that is, put parentheses inside parentheses. When a formula contains nested parentheses, Excel evaluates the most deeply nested expressions first and works its way out. The following example of a formula uses nested parentheses:

=((B2*C2)+(B3*C3)+(B4*C4))*B6

The preceding formula has four sets of parentheses. Three sets are nested inside the fourth set. Excel evaluates each nested set of parentheses and then sums the three results. This sum is then multiplied by the value in B6.

Make liberal use of parentheses in your formulas even when they aren’t necessary. Using parentheses clarifies the order of operations and makes the formula easier to read. For example, if you want to add 1 to the product of two cells, the following formula does the job:

=A1*A2+1

Because of Excel’s operator precedence rules, the multiplication will be performed before the addition. Therefore, parentheses are not necessary. You may find it much clearer, however, to use the following formula even though it contains superfluous parentheses:

=(A1*A2)+1

Creating an absolute or a mixed reference

When you create a formula by pointing to cells, all cell and range references are relative. To change a reference to an absolute reference or a mixed reference, you must do so manually by adding the dollar signs. Or when you’re entering a cell or range address, you can press the F4 key to cycle among all possible reference modes.

If you think about it, you may realize that the only reason you would ever need to change a reference is if you plan to copy the formula.

Figure 2.3 demonstrates an absolute reference in a formula. Cell D2 contains a formula that calculates a final sales price based on the quantity (cell B2), price (cell C2) and the sales tax (cell B7):

=(B2*C2)*(1+$B$7)

The reference to cell B7 is an absolute reference. When you copy the formula in cell D2 to the cells below, the $B$7 reference always points to the sales tax cell. Using a relative reference (B7) results in incorrect results in the copied formulas.

Figure 2.4 demonstrates the use of mixed references. Note the formula in cell J3:

=SUM($I$2:I3)

This formula calculates a running total for Units Sold. Because the formula uses absolute references to row 2 and column I, each copied formula sums all Units Sold starting from cell I2. If the formula used relative references, copying the formula would cause that reference to adjust and produce the wrong results.

Referencing other sheets or workbooks

A formula can use references to cells and ranges that are in a different worksheet. To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point. Note this example of a formula that uses a cell reference in a different worksheet (Sheet2):

=Sheet2!A1+1

You can also create link formulas that refer to a cell in a different workbook. To do so, precede the cell reference with the workbook name (in square brackets), the worksheet name, and an exclamation point (!), like this:

=[Budget.xlsx]Sheet1!A1+1

If the workbook name or sheet name in the reference includes one or more spaces, you must enclose it (and the sheet name) in single quotation marks. For example:

='[Budget Analysis.xlsx]Sheet1'!A1+A1

If the linked workbook is closed, you must add the complete path to the workbook reference. For example:

='C:MSOfficeExcel[Budget Analysis.xlsx]Sheet1'!A1+A1

A linked file can also reside on another system that’s accessible on your corporate network. The following formula refers to a cell in a workbook in the files directory of a computer named DataServer:

='\DataServerfiles[Budget Analysis.xlsx]Sheet1'!A1

If the linked workbook is stored on the Internet, the formula also includes the uniform resource locator (URL). For example:

='https://d.docs.live.net/86a61fd208/files/[Annual Budget.xlsx]Sheet1'!A1

Although you can enter link formulas directly, you can also create the reference by using the normal pointing methods discussed earlier. To do so, make sure the source file is open. Normally, you can create a formula by pointing to results in relative cell references. But when you create a reference to another workbook by pointing, Excel always creates absolute cell references. If you plan to copy the formula to other cells, you must edit the formula to make the references relative.

A goal seeking example

Figure 2.8 shows a loan payment calculator that shows the expected monthly payment based on a given loan amount, number of payment months, and interest rate.

Imagine that you know you can afford a payment of $500 per month. Knowing you can get a fixed-rate of 7 percent over 60 payment months, what is the maximum loan you can take and still have a payment of $500?

In other words, what value in cell N1 causes the formula in cell N5 to yield $500? You can plug values into cell N1 until N5 displays $500. Or you can let Excel determine the answer.

To answer this question, choose Data ➜ Data Tools ➜ What-If Analysis ➜ Goal Seek. Excel displays the Goal Seek dialog box, as shown in Figure 2.9. Completing this dialog box resembles forming the following sentence: set cell N5 to 500 by changing cell N1. Clicking OK begins the goal seeking process.

Almost immediately, Excel announces that it has found the solution and displays the Goal Seek status box (see Figure 2.10). This box tells you the target value and what Excel came up with. In this case, Excel found that a loan amount of $25,251 would yield $500 monthly payments.

You can click OK to replace the original value with the found value, or you can click Cancel to restore your worksheet to its original form before you chose Goal Seek.

More about goal seeking

If you think about it, you may realize that Excel can’t always find a value that produces the result you’re looking for—sometimes a solution doesn’t exist. In such a case, the Goal Seek Status box informs you of that fact. Other times, however, Excel may report that it can’t find a solution even though you believe one exists. In this case, you can adjust the current value of the changing cell to a value closer to the solution and then reissue the command. If that fails, double-check your logic and make sure that the formula cell does indeed depend on the specified changing cell.

Like all computer programs, Excel has limited precision. To demonstrate this, enter =A1^2 into cell A2. Then choose Data ➜ Data Tools ➜ What-If Analysis ➜ Goal Seek to find the value in cell A1 that causes the formula to return 16. Excel returns a value of 4.00002269—close to the square root of 16, but certainly not exact. You can adjust the precision in the Calculation section of the Formulas tab in the Excel Options dialog box (make the Maximum change value smaller).

In some cases, multiple values of the input cell produce the same desired result. For example, the formula =A1^2 returns 16 if cell A1 contains either –4 or +4. If you use goal seeking when two solutions exist, Excel gives you the solution that is nearest to the current value in the cell.

Perhaps the main limitation of the Goal Seek command is that it can find the value for only one input cell. For example, it can’t tell you what purchase price and what down payment percent result in a particular monthly payment. If you want to change more than one variable at a time, use the Solver add-in.