Microsoft Office Tutorials and References

In Depth Information

When the formula in a cell refers to that cell, the cause of the circular reference is quite obvious and is, there-

fore, 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 for-

mula result should be, Excel can tell you which values of one or more input cells you need to produce that res-

ult. 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-9 shows a mortgage loan worksheet that has four input cells (C4:C7) and four formula cells

(C10:C13). The formulas calculate various values using the input cell. The formulas are

C10: =(1–C5)*C4

C11: =PMT(C7/12,C6,–C10)

C12: =C11*C6

C13: =C12–C10