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
Search JabSto ::

Custom Search