<<

. 19
( 62 .)



>>

Another variation is this formula:

= 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
( 62 .)



>>