ñòð. 9 |

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 |