Chapter 2
Basic Facts About Formulas

In This Chapter

  • How to enter, edit, and paste names into formulas
  • The various operators used in formulas
  • How Excel calculates formulas
  • Cell and range references used in formulas
  • Copying and moving cells and ranges
  • How to make an exact copy of a formula
  • How to convert formulas to values
  • How to prevent formulas from being viewed
  • The types of formula errors
  • Circular reference messages and correction techniques
  • Excel’s goal seeking feature

This chapter serves as a basic introduction to using formulas in Excel. Although it’s intended primarily for newcomers to Excel, even veteran Excel users may find some new information here.

Entering and Editing Formulas

This section describes the basic elements of a formula. It also explains various ways of entering and editing your formulas.

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.

Entering a formula manually

Entering a formula manually involves, well, entering a formula manually. You simply activate a cell and type an equal sign (=) followed by the formula. As you type, the characters appear in the cell as well as in the Formula bar. You can, of course, use all the normal editing keys when typing a formula. After you insert the formula, press Enter.

After you press Enter, the cell displays the result of the formula. The formula itself appears in the Formula bar when the cell is activated.

Entering a formula by pointing

The other method of entering a formula that contains cell references still involves some manual typing, but you can simply point to the cell references instead of typing them manually. For example, to enter the formula =A1+A2 into cell A3, follow these steps:

  1. Move the cell pointer to cell A3.
  2. Type an equal sign (=) to begin the formula.

    Notice that Excel displays Enter in the left side of the status bar.

  3. Press the up arrow twice.

    As you press this key, notice that Excel displays a moving border around the cell and that the cell reference (A1) appears in cell A3 and in the Formula bar. Also notice that Excel displays Point on the status bar.

    If you prefer, you can use your mouse and click cell A1.

  4. Type a plus sign (+).

    The moving border becomes a solid blue border around A1, and Enter reappears in the status bar. The cell cursor also returns to the original cell (A3).

  5. Press the up arrow one more time. If you prefer, you can use your mouse and click cell A2.

    A2 is appended to the formula.

  6. Press Enter to end the formula.

    As with typing the formula manually, the cell displays the result of the formula, and the formula appears in the Formula bar when the cell is activated.

    If you prefer, you can use your mouse and click the check mark icon next to the Formula bar instead of pressing Enter. And if, at any time, you change your mind about entering the formula, just press Esc or click the X icon next to the Formula bar.

This method might sound a bit tedious, but it’s actually very efficient after you get the hang of it. Pointing to cell addresses rather than entering them manually is almost always faster and more accurate.

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.

Image described by surrounding text.

Figure 2.1 Using Formula AutoComplete to enter a range name into a formula.

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.

Screenshot of a cell in an excel sheet shows a sum formula inside an if loop with conditions met goal and did not meet goal. The cancel, enter and insert functions are represented using a cross, tick and fx.

Figure 2.2 This formula contains spaces 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.

Using Operators in Formulas

As previously discussed, an operator is one of the basic elements of a formula. An operator is a symbol that represents an operation. Table 2.1 shows the Excel-supported operators.

Table 2.1 Excel-Supported Operators

Symbol Operator
+ Addition
Subtraction
/ Division
* Multiplication
% Percent*
& Text concatenation
^ Exponentiation
= Logical comparison (equal to)
> Logical comparison (greater than)
< Logical comparison (less than)
>= Logical comparison (greater than or equal to)
<= Logical comparison (less than or equal to)
<> Logical comparison (not equal to)

*Percent isn’t really an operator, but it functions similarly to one in Excel. Entering a percent sign after a number divides the number by 100. If the value is not part of a formula, Excel also formats the cell as percent.

Reference operators

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

Table 2.2 Reference Operators

Symbol Operator
: (colon) Range. Produces one reference to all the cells between two references.
, (comma) Union. Combines multiple cell or range references into one reference.
(single space) Intersection. Produces one reference to cells common to two 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.

Table 2.3 Operator Precedence in Excel Formulas

Symbol Operator
Colon (:), comma (,), space( ) Reference
Negation
% Percent
^ Exponentiation
* and / Multiplication and division
+ and – Addition and subtraction
& Text concatenation
=, <, >, <=, >=, and <> Comparison

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

Calculating Formulas

You’ve probably noticed that the formulas in your worksheet are calculated immediately. If you change any cells that the formula uses, the formula displays a new result with no effort on your part. This occurs when Excel’s Calculation mode is set to Automatic. In this mode (the default mode), Excel follows certain rules when calculating your worksheet:

  • When you make a change (enter or edit data or formulas, for example), Excel calculates immediately those formulas that depend on new or edited data.
  • If working on a lengthy calculation, Excel temporarily suspends calculation when you need to perform other worksheet tasks; it resumes when you finish.
  • Formulas are evaluated in a natural sequence. For instance, if a formula in cell D12 depends on the result of a formula in cell F12, cell F12 is calculated before D12.

Sometimes, however, you may want to control when Excel calculates formulas. For example, if you create a worksheet with thousands of complex formulas, you may find that things can slow to a snail’s pace while Excel does its thing. In this case, you can set Excel’s Calculation mode to Manual. Do this by choosing Formulas ➜ Calculation ➜ Calculation Options ➜ Manual.

When you work in manual Calculation mode, Excel displays Calculate in the status bar when you have any uncalculated formulas.

The Formulas ➜ Calculation group contains two controls that, when clicked, perform a calculation: Calculate Now and Calculate Sheet. In addition to these controls, you can click the Calculate word on the status bar or use the following shortcut keys to recalculate the formulas:

  • F9: Calculates the formulas in all open workbooks (same as the Calculate Now control).
  • Shift+F9: Calculates only the formulas in the active worksheet. It does not calculate other worksheets in the same workbook (same as the Calculate Sheet control).
  • Ctrl+Alt+F9: Forces a complete recalculation of all open workbooks. Use it if Excel (for some reason) doesn’t seem to return correct calculations.
  • Ctrl+Shift+Alt+F9: Rechecks all the dependent formulas and then forces a recalculation of all open workbooks.

Cell and Range References

Most formulas reference one or more cells by using the cell or range address (or the name if it has one). Cell references come in four styles; the use of the dollar sign symbol differentiates them:

  • Relative: The reference is fully relative. When you copy the formula, the cell reference adjusts to its new location.

    Example: A1

  • Absolute: The reference is fully absolute. When you copy the formula, the cell reference does not change.

    Example: $A$1

  • Row Absolute: The reference is partially absolute. When you copy the formula, the column part adjusts, but the row part does not change.

    Example: A$1

  • Column Absolute: The reference is partially absolute. When you copy the formula, the row part adjusts, but the column part does not change.

    Example: $A1

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):

Image described by surrounding text.

Figure 2.3 This worksheet demonstrates the use of an absolute reference.

=(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:

Image described by surrounding text.

Figure 2.4 An example of using mixed references in a formula.

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

Copying or Moving Formulas

As you create a worksheet, you may find it necessary to copy or move information from one location to another. Excel makes copying or moving ranges of cells easy. Here are some common things you might do:

  • Copy a cell to another location. Contents of the source cell are duplicated in the destination cell.
  • Copy a cell to a range of cells. The source cell is copied to every cell in the destination range.
  • Copy a range to another range. Both ranges must be the same size.
  • Move a range of cells to another location. Contents are removed from the source cell and relocated to the destination.

The primary difference between copying and moving a range is the effect of the operation on the source range. When you copy a range, the source range is unaffected. When you move a range, the contents are removed from the source range.

Copying or moving consists of two steps (although shortcut methods are available):

  1. Select the cell or range to copy (the source range) and copy it to the Clipboard. To move the range instead of copying it, cut the range rather than copying it.
  2. Move the cell pointer to the range that will hold the copy (the destination range), and paste the Clipboard contents.

When you copy a range that contains formulas, the cell references in the formulas are adjusted. When you move a range that contains formulas, the cell references in the formulas are not adjusted. This is almost always what you want.

Making an Exact Copy of a Formula

When you copy a formula, Excel adjusts the formula’s cell references when you paste it to a different location. Usually, adjusting the cell references is exactly what you want. Sometimes, however, you may want to make an exact copy of the formula. You can do this by converting the cell references to absolute references, as discussed earlier—but this isn’t always desirable.

A better approach is to select the formula while in edit mode and then copy it to the Clipboard as text. There are several ways to do this. Here we present a step-by-step example of how to make an exact copy of the formula in A1 and copy it to A2:

  1. Select cell A1 and press F2 to activate edit mode.
  2. Press Ctrl+Home to move the cursor to the start of the formula, followed by Ctrl+Shift+End to select all the formula text.

    Or you can drag the mouse to select the entire formula.

    Note that holding down the Ctrl key is necessary when the formula is more than one line long, but it’s optional for formulas that are a single line.

  3. Choose Home ➜ Clipboard ➜ Copy (or press Ctrl+C).

    This copies the selected text to the Clipboard.

  4. Press Esc to end edit mode.
  5. Activate cell A2.
  6. Press F2 for edit mode.
  7. Choose Home ➜ Clipboard ➜ Paste (or press Ctrl+V), followed by Enter.

    This operation pastes an exact copy of the formula text into cell A2.

You can also use this technique to copy just part of a formula to use in another formula. Just select the part of the formula that you want to copy by dragging the mouse or by pressing the Shift+arrow keys. Then use any of the available techniques to copy the selection to the Clipboard. After that, you can paste the text to another cell.

Formulas (or parts of formulas) copied in this manner won’t have their cell references adjusted when you paste them to a new cell. This is because you copy the formulas as text, not as actual formulas.

Another technique for making an exact copy of a formula is to edit the formula and remove its initial equal sign. This converts the formula to text. Then copy the “nonformula” to a new location. Finally, edit both the original formula and the copied formula by inserting the initial equal sign.

Converting Formulas to Values

If you have a range of formulas that always produce the same result (that is, dead formulas), you may want to convert them to values. You can use the Home ➜ Clipboard ➜ Paste ➜ Values command to do this.

Suppose that range A1:A10 contains formulas that calculate results that never change. To convert these formulas to values, do the following:

  1. Select A1:A10.
  2. Choose Home ➜ Clipboard ➜ Copy (or press Ctrl+C).
  3. Choose Home ➜ Clipboard ➜ Paste ➜ Values (V).
  4. Press Enter or Esc to cancel paste mode.

You can also take advantage of a drop-down control that shows paste options. In step 3 in the preceding list, press Ctrl+V to paste. A drop-down list appears at the lower-right corner of the range. Click and select one of the Paste Values icons (see Figure 2.5).

Image described by caption.

Figure 2.5 Choosing a paste option after pasting data.

Converting formulas to values is useful when you use formulas as a means to convert cells. For example, assume that you have a list of names (in uppercase) in column A. You want to convert these names to proper case. To do so, you need to create formulas in a separate column; then convert the formulas to values and replace the original values in column A. The following steps illustrate how to do this:

  1. Insert a new column after column A.
  2. Insert the following formula into cell B1:

    =PROPER(A1)
  3. Copy the formula down column B to accommodate the number of entries in column A.

    Column B then displays the values in column A, but in proper case.

  4. Select all the names in column B.
  5. Choose Home ➜ Clipboard ➜ Copy.
  6. Select cell A1.
  7. Choose Home ➜ Clipboard ➜ Paste ➜ Values.
  8. Press Enter or Esc to cancel paste mode.
  9. Delete column B.

Hiding Formulas

In some cases, you may not want others to see your formulas. For example, you may have a special formula you developed that performs a calculation proprietary to your company. You can use the Format Cells dialog box to hide the formulas contained in these cells.

To prevent one or more formulas from being viewed:

  1. Select the formula or formulas.
  2. Right-click and choose Format Cells to show the Format Cells dialog box (or press Ctrl+1).
  3. In the Format Cells dialog box, click the Protection tab.
  4. Place a check mark in the Hidden check box, as shown in Figure 2.6.
  5. Use the Review ➜ Changes ➜ Protect command to protect the worksheet.

    To prevent others from unprotecting the sheet, specify a password in the Protect Sheet dialog box.

Image described by surrounding text.

Figure 2.6 Use the Format Cells dialog box to change the Hidden and Locked status of a cell or range.

By default, all cells are locked. Protecting a sheet prevents any locked cells from being changed. So you should unlock any cells that require user input before protecting your sheet.

Errors in Formulas

It’s not uncommon to enter a formula only to find that the formula returns an error. Table 2.4 lists the types of error values that may appear in a cell that has a formula.

Table 2.4 Excel Error Values

Error Value Explanation
#DIV/0! The formula attempts to divide by zero (an operation not allowed on this planet). This also occurs when the formula attempts to divide by an empty cell.
#NAME? The formula uses a name that Excel doesn’t recognize. This can happen if you delete a name used in the formula or if you misspell a function.
#N/A The formula refers (directly or indirectly) to a cell that uses the NA function to signal unavailable data. This error also occurs if a lookup function does not find a match.
#NULL! The formula uses an intersection of two ranges that don’t intersect. (We describe range intersection in Chapter 3.)
#NUM! A problem occurs with a value; for example, you specify a negative number where a positive number is expected.
#REF! The formula refers to an invalid cell. This happens if the cell has been deleted from the worksheet.
#VALUE! The formula includes an argument or operand of the wrong type. An operand refers to a value or cell reference that a formula uses to calculate a result.

Dealing with Circular References

When you enter formulas, you may occasionally see a message from Excel like the one shown in Figure 2.7. This indicates that the formula you just entered will result in a circular reference.

Image described by surrounding text.

Figure 2.7 Excel’s way of telling you that your formula contains a circular reference.

A circular reference occurs when a formula refers to its own value, either directly or indirectly. For example, if you type =A1 into cell A3, =A3 into cell B3, and =B3 into cell A1, it produces a circular reference because the formulas create a circle in which each formula depends on the one before it. Every time the formula in A3 is calculated, it affects the formula in B3, which in turn affects the formula in A1. The result of the formula in A1 then causes A3 to recalculate, and the calculation circle starts all over again.

When you enter a formula that contains a circular reference, Excel displays a dialog box with two options: OK and Help.

Normally, you’ll want to correct any circular references, so you should click OK. After you do so, Excel inserts tracing arrows. Clicking the Help option displays the Excel Help topic for circular references.

The status bar displays Circular References: A3, in this case. To resolve the circular reference, choose Formulas ➜ Formula Auditing ➜ Error Checking ➜ Circular References to see a list of the cells involved in the circular reference. Click each cell in turn and try to locate the error. If you cannot determine whether the cell is the cause of the circular reference, navigate to the next cell on the Circular References submenu. Continue reviewing each cell on the Circular References submenu until the status bar no longer reads Circular References.

After you enter a formula that contains a circular reference, Excel displays a message in the status bar reminding you that a circular reference exists. In this case, the message reads Circular References: A3. If you activate a different worksheet or workbook, the message simply displays Circular References (without the cell reference).

When the formula in a cell refers to that cell, the cause of the circular reference is quite obvious and is, therefore, easy to identify and correct. For this type of circular reference, Excel does not show tracer arrows. For an indirect circular reference, as in the preceding example, the tracer arrows can help you identify the problem.

Goal Seeking

Many spreadsheets contain formulas that enable you to ask questions such as, “What would be the total profit if sales increase by 20 percent?” If you set up your worksheet properly, you can change the value in one cell to see what happens to the profit cell.

Goal seeking serves as a useful feature that works in conjunction with your formulas. If you know what a formula result should be, Excel can tell you which values of one or more input cells you need to produce that result. In other words, you can ask a question such as, “What sales increase is needed to produce a profit of $1.2 million?”

Single–cell goal seeking (also known as backsolving) represents a rather simple concept. Excel determines what value in an input cell produces a desired result in a formula cell. You can best understand how this works by walking through an example.

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.

Image described by surrounding text.

Figure 2.8 This worksheet presents a simple demonstration of goal seeking.

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.

Image described by surrounding text.

Figure 2.9 The Goal Seek dialog box.

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.

Image described by surrounding text.

Figure 2.10 The Goal Seek Status dialog box.

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.117.105.74