<<

. 9
( 62 .)



>>


TLFeBOOK
Chapter 3
44




between each iteration become progressively smaller. It is this
kind of calculation that tells us that circular references can be
useful. Don™t let anyone tell you that circular references must be
avoided at all times, or that having circular references in a model
is a sign of bad modeling. If anything, having circular references
that work is a sign of sophisticated modeling.
By the way, the interest calculations used above have been
highly simplified. In actual calculations, we might want to con-
sider that the interest expense is calculated on the average of the
beginning and ending plug numbers. We will discuss this more
fully in later chapters.


Some Warning Signs You Might See
If you put in a circular reference and the Iteration is not on, you
will see the alert shown in Figure 3-18 from Excel. In this case,
either turn the Iteration setting on if you want to have the cir-
cular reference or just correct your formula if you put in the
circular reference by mistake. Notice that the message on this
alert box does not consider the possibility that you might actually
have wanted to put in the circular reference.
If the Iteration setting is on, but you have somehow entered
the wrong relationships in the circular formulas, your num-
bers go berserk and become astronomically huge. This happens
when the numbers diverge, rather than converge. Luckily,
Excel has a setting that limits the number of iterations it will
perform.


FIGURE 3“18




TLFeBOOK
Starting Out 45




Maximum Iterations
This is the number of iterations that it will perform. The max-
imum is 200. The kind of model we are building should iterate
no more than 25 times or so. Recommendation: set it to 100,
which will definitely give you an indication if something is not
calculating properly, without having to wait for the second
hundred iterations.

Maximum Change
This is the maximum change setting below which the iterations
will stop. The change describes the biggest change in each of the
numbers in the worksheet between one iteration and the next.
The smaller the maximum change setting, the more iterations
that Excel will have to make. Recommendation: 0.001 is fine. If
the maximum iterations setting is 100, but the maximum change
of 0.001 is reached in 5 iterations, then Excel will stop after
5 iterations.




TLFeBOOK
This page intentionally left blank.




TLFeBOOK
CHAPTER 4


Your Model-Building
Toolbox: F Keys and
Ranges




In Chapter 3 we went through the Excel controls ˜˜outside™™ the
spreadsheet area (i.e., the settings that control how the spread-
sheet itself looks and functions). In this chapter we will go over
specific keyboard controls and look at how Excel considers
blocks of cells in the spreadsheet area, specifically:
The F keys on your keyboard
u

Range names in Excel
u




F KEYS
Along the top row of your keyboard is a series of keys marked
F1 to F12. Each of these ™™F keys™™ launches specific commands in
Excel, but you will find that the ones you will be using often are
really only about half of the F keys. The ones used more often are
shown in bold:
F1 Help
u

F2 Edit the active cell
u

F3 Paste a name into a formula
u

F4 Repeat the last keyboard action
u

F5 Go to
u


47



Copyright © 2004 by John S. Tjia. Click here for terms of use.
TLFeBOOK
Chapter 4
48




F6 Move to the next pane
u

F7 Check spelling
u

F8 Anchors the start of a range
u

F9 Calculate all sheets in all open workbooks
u

F10 Make the menu bar active
u

F11 Create a chart
u

F12 Save As. . . command
u


Additionally, there are four other frequently used sequences
that use the F keys in combination. These are:
F2 þ F4 Cycle through absolute references
u

F2 þ F5 Trace back to formula sources
u

F2 þ F9 Recalculate portions of formulas
u

Ctrl þ Alt þ F9 Forced calculation
u


In addition to these, Excel gives you even more commands
if these F keys are used with Shift, Ctrl, Alt, Ctrl þ Shift, or
Alt þ Shift. Most of these are not often used in day-to-day mod-
eling, but you should explore the full list of functions by going to
F1 Help. Type any of the F keys (e.g., F1) in the input box at the
top of the Find tab. Then select the ˜˜Function keys in Microsoft
Excel™™ in the list of items shown in the list box at the bottom of
the form for a full list of the function keys in Excel.


F1: Help
This is the key you use to get help from Excel.


F2: Edit
Pressing this key will bring you to the ˜˜edit mode.™™ It will allow
you to edit the cell that your cursor is on. Pressing this key and
other F keys will have other results (see below).
In the F2 Edit mode, you can choose to edit the formula
directly in the cell that the cursor is on, or in the formula bar
near the top of the screen. In both cases, the cells of the precedent
cells (the cells that feed the current cell, i.e., the ones carrying




TLFeBOOK
Your Model-Building Toolbox: F Keys and Ranges 49




data that ˜˜precede™™ the current cell) will be highlighted by
different-colored borders. The setting to determine this is in the
Tools > Options > Edit tab, with the check box marked ˜˜Edit
directly in cell.™™
This choice is a matter of personal preference, but there is
also one important difference. If the ˜˜Edit directly in cell™™ is
checked, double-clicking a cell has the same effect as pressing
F2; it just gets you into the Edit mode. However, if this is not
checked, double-clicking on a cell will ˜˜jump™™ you to the pre-
cedent cell (or the first precedent address if there is a long for-
mula in that cell). A precedent cell is the cell that contains data
that is being used in the cell that you are on; it is the cell whose
data ˜˜precedes™™ the current one. The opposite of this, referring
to the cell that makes use of the data in the current cell, is the
dependent cell.


F2 þ F4: Cycle Through Absolute References
Cell addresses in Excel change automatically when you copy
them from cell to cell. The cell in B2, shown in the box as
the formula, will change as it is copied into different cells.
Across the same row, the column letter reference will change;
down the same column, the row number reference will change.


A B C D E F G
1
2 =B4 =D4 =E4 =F4
3 =B5
4



In some instances, it would be preferable not to have this
relative referencing work. When we copy the cell to other places,
we can make an absolute reference. We do this by putting the
dollar sign ˜˜$™™ in front of the column letter or the row number,
or both. There are four possibilities for specifying the B4 address,
and pressing F4 successively after you are in the F2 Edit mode
will cycle you through these settings.




TLFeBOOK
Chapter 4
50




As you copy:
B4: Both the column letter and the row number will
u

move.
$B$4: Both stay unchanged.
u

B$4: The column letter will change, but the row number
u

will stay unchanged.
$B4: The column letter stays unchanged, but the row
u

number will change.

<<

. 9
( 62 .)



>>