ñòð. 19 |

= IFÃ°D12,D10=D12,â€œnaâ€Ãž

This formula will return the text â€˜â€˜naâ€™â€™ if D12 is zero. This is

fine, unless there is the chance that this formula will be read by

another cell as part of the calculation in that cell. If the first cell

shows â€˜â€˜na,â€™â€™ then the calculation in the second cell will run into

trouble because it will not be able to use this text in its calcula-

tions. That second cell will show the #VALUE! error (see below).

One trick you can use if you do want the â€˜â€˜naâ€™â€™ to show but

avoid having other cells running into calculation problems is to

use the first formula that returns a 0. To do this, we use Excelâ€™s

formatting capabilities to show â€˜â€˜naâ€™â€™ when it is the value for 0.

Please turn to Chapter 18, â€˜â€˜Bells and Whistlesâ€™â€™ to see how this

is done.

TLFeBOOK

Chapter 5

98

The #VALUE! Error

The usual occurrence of this error message is when you have

written a formula whose components include a text. Essentially,

this is Excel saying: â€˜â€˜I donâ€™t want text. I want values only,

please.â€™â€™

The #NAME? Error

You will get this error if your formula uses a range name that

does not exist. This can be because of a misspelled range name,

or a range name that you have created and used before but

which has now been deleted. A misspelled function name will

also give you this error.

The #REF! Error

This happens when the formula uses an invalid cell reference.

For example, start with a formula in cell A10 like this:

= A1+10

If you copy this up one row, the formula will return a #REF!. In

copying, Excel will try to keep the relative referencing, so as you

go up one row, it changes the reference to A0. Since A0 does not

exist, the error message shows up.

You will also get this error if you are in a situation where a

formula, looks like this:

= B10+C10

and you cut and paste something into B10 and/or C10. Copying

and pasting will not cause a #REF! error, however.

The ISERROR Function for Trapping Errors

Errors in a spreadsheet are generally easy to find and correct.

Understanding what the error messages mean allows us to get an

idea of what kind of error to look for and make the necessary

corrections.

TLFeBOOK

Your Model-Building Toolbox: Functions 99

When we build a model, however, errors can be a little more

troublesome. As you will see when we start developing the for-

mulas in the model, we will be using circular references.

A circular reference occurs when a formula refers to itself,

whether directly or indirectly. For example, if you enter

Â¼SUM(A1 : A10) in cell A10, you would get a circular reference

because every time A10 is calculated, it must include itself in the

SUM calculation, in a never-ending cycle.

When you create such a circular reference, Excel will give

you a warning message:

There are two ways of dealing with circular references and

Excelâ€™s attempts to warn you about them:

1. One way is to correct the inadvertent circular references.

Clicking on the OK on this message form will get Excelâ€™s

circular reference toolbar to show, which will help you to

start to zero in on where the troublesome reference is.

2. The second way, and this is only if you actually want to

have circular references, is to set Excelâ€™s calculation to the

iteration mode by the following sequence: Tools >

Options > Calculation > Iteration. This will set Excel to

allow circular references. An iteration is simply a cycle of

calculation in which you can imagine a â€˜â€˜waveâ€™â€™ of

calculation sweeping through the whole model, sheet

by sheet.

Why do we want iterative circular references? Simply put, it

is a simple way to get the model to converge on its calculations

of interest expense. As you will see in the next chapters, in the

forecast years the model will be creating â€˜â€˜plugâ€™â€™ numbers to

balance the balance sheet. A plug number that is assumed to

be debt will create an additional interest expense, which will

TLFeBOOK

Chapter 5

100

affect the plug number itself. The model then has to recalculate

(or iterate) to adjust for this increase, which in turn will create

another, but incrementally smaller, increase. The iteration will go

through several more cycles before there is convergence within

the limits set in the model.

So, circular references can be used to good advantage. The

dangerous thing is that as there is now a calculation loop in the

model, if there is an error that gets inadvertently introduced into

the calculations, this error message will continue to cycle around

in the loop. Even after the source of the error has been removed,

the error message continues to be caught in the loop! In this case,

there are two ways to correct the situation:

1. Manually change one of the formulas so that the

calculation loop is â€˜â€˜broken.â€™â€™ Make sure that the source of

the original error is corrected, and recalculate the model

again to clear out the error message. Once this is done,

restore the formula again.

2. A more elegant way is to use the ISERROR function to

trap the error. Simply put, at a location in the calculation

loop, we write a formula that returns a 0 when it

encounters an error condition. Letâ€™s say we put this in

cell C51:

= IFÃ°ISERRORÃ°C50Ãž,0,C50Ãž

C50 is a cell that is part of the loop, as is C51. If there is an

error in the loop, this formula in C50 will revert to a 0,

which is then read by C51 and the rest of the calculations.

The 0 breaks the circular loop and gives the error message

a chance to be cleared out, so that when C50 calculates

again, it does not see the error sign and automatically

reconnects to C50. The loop is restored.

OTHER FUNCTIONS TO KNOW

Working with Parts of Numbers:

ROUND, INT, MOD

ROUND(number, number of digits) will round the number

u

to the specified number of digits. Â¼ROUND(1.564,1) will

TLFeBOOK

Your Model-Building Toolbox: Functions 101

return 1.6, for example. Likewise, Â¼ROUND(1.564,0) will

return 2.

Neat trick 1: You can put a negative number for the

number of digits: Ã€1 will round the number to the nearest

ten, Ã€2 to the nearest hundred, Ã€3 to the nearest thousand,

etc. Thus, Â¼ROUND(1,234.2,Ã€2) will return 1,200.

Neat trick 2: To round to the nearest 5 cents in a price,

double the number first before doing a ROUND to 1 digit,

and then divide by 2. The nearest 5 cents for $1.96 is

$1.95. We get there by Â¼ROUND(1.96 * 2,1)/2.

INT(number) will get you the integer part of the number,

u

the part to the left of the decimal point.

MOD(number, divisor) will return the remainder of the

u

number after it has been divided by the divisor. To get to

the part of a number to the right of the decimal point you

must use 1 as the divisor, thus, Â¼MOD(1.564,1) will return

0.564. You can use other divisors for other results. For

example, Â¼MOD(1.564,0.5) returns 0.064. The divisor can

be any other number except 0.

Working with Numbers in a Direction: ABS,

CEILING, FLOOR, ROUNDUP, ROUNDDOWN

We can imagine that numbers lie on a line, with the 0 point

separating the negative and the positive numbers. The following

functions work in identifying or shifting numbers on the

numbers line.

ABS(number) returns the value of a number without the

u

sign. Thus Â¼ABS(Ã€5) and ABS(5) will both yield 5, or the

measure of the distance of 5 from the 0 point on the

numbers line.

CEILING(number,significance) returns the number rounded

u

away from zero. For a positive number, this means it is

rounded up. Â¼CEILING(2.01,1) returns 3. For a negative

number, it is rounded down, so it becomes more negative.

CEILING(Ã€2.01,Ã€1) returns Ã€3. Both the number and

significance must have the same sign. If they do not, a

#NUM! error results.

TLFeBOOK

Chapter 5

102

FlOOR(number, significance) is similar to CEILING, but

u

rounds the number toward zero. FLOOR(1.99,1) returns 1.

FLOOR(Ã€1.99,Ã€1) returns Ã€1. Similar to CEILING, both

the number and significance must have the same sign.

If they do not, a #NUM! error results.

ROUNDUP(number, number of digits) behaves like ROUND,

u

but rounds a number away from zero (in this sense it also

works like CEILING). The number of digits can be a

positive or negative number, and works exactly the same

way as in ROUND.

ROUNDDOWN(number, number of digits) is similar to

u

ROUNDUP, but rounds the number toward zero.

Other Information Functions: ISNUMBER,

ISTEXT, ISBLANK

These functions are useful for determining the kind of informa-

tion that you are dealing with.

ISNUMBER(Value) will return a TRUE if value is a number

u

and a FALSE if it is a text or is blank. One example where

this is useful is when you are writing an IF statement

where you want the formula to read the entry in a cell,

even if it is a zero. (The usual IF statement testing for the

value â€˜â€˜if it is not a 0â€™â€™ will return a FALSE when it

encounters the number 0.)

ISTEXT(Value) will return a TRUE if the value is a text

u

string.

ISBLANK(Value) will return a TRUE if the value refers to

u

an empty cell.

Working with Text: LEFT, RIGHT, MID, LEN,

LOWER, UPPER, PROPER, TEXT, VALUE

These functions are useful when you work with text strings. In

these functions, the space between words counts as one character.

LEFT(Text, number of characters) will return the leftmost

ñòð. 19 |