<<

. 12
( 62 .)



>>



3. A user form like that shown in Figure 4-3 will appear.

FIGURE 4“3




4. Click on OK.
Done! The user form explains, if rather cryptically, that Excel
is creating the named ranges based on the names in the left
column of the block you have highlighted. By this approach,
you could also have created named ranges based on labels you
have into the top, bottom, or right of the highlighted block.




TLFeBOOK
Chapter 4
62




Making Range Names More Informative
Use range names that are clear and spell out the name as much
as possible, while keeping it easy to type. In the example,
˜˜TaxRate™™ is more immediately clear than ˜˜TxRt.™™ If you are
going to be typing this range name many times as you develop
your model, strike a good balance between clarity and ease of
typing.
You can also use range names to describe switches. For
instance, you may have a cell that holds the switch for the use
of a U.S. tax rate or a foreign tax rate for a tax calculation, which
will be either a 1 or a 0, respectively. If you named the cell
˜˜TaxRate,™™ you would have no easy way to remember what 0
or 1 means. You may remember this in the current modeling
session, but there is a good chance that when you continue
your work the next day you won™t remember it as easily. In
this case, consider making the range name more self-explanatory.
Instead of ˜˜TaxRate,™™ call the toggle cell in A3 ˜˜TaxUS1Frn0™™
instead. So it may look like this in a formula:

= =C4*IF(TaxUS1Frn=1,C5,C6)
C7
A B C D E F G H
1
2 TaxUS1Frn0 2002 2003 2004 2005 2006
3 1
4 Earnings before tax 100 110 120 130 140
5 U.S. rate 35% 35% 35% 35% 35%
6 Foreign rate 21% 21% 21% 21% 21%
7 Tax 35 38.5 42 45.5 49
8 Net income 65 71.5 78 84.5 91
9
10




Deleting a Range Name
To delete a range name, do the Insert > Name > Define sequence.
At the dialog box that you see, select the range name in the list
box and then click on the Delete button.




TLFeBOOK
CHAPTER 5


Your Model-Building
Toolbox: Functions




In this chapter, we continue to look at the spreadsheet aspect of
Excel and what we can do with it. We have gone over F keys and
ranges, but now we come to the true power of Excel: its ability,
through preformatted formulas called functions, to do a whole
host of calculations.


FUNCTIONS
Functions allow you to do arithmetic and other operations very
quickly and conveniently. Excel has a list of functions that it
offers, and you can see them by clicking on the icon that looks
like fx on the formula bar (in versions earlier than XP, the fx is
visible in the toolbar). This will display the full list of functions.
In XP you can also see the functions by clicking on the drop
down arrow on the icon that looks like the Greek sigma (Æ)
and selecting More Functions.
There are some 230 functions in all, in 10 major classes, but
you do not need to be familiar with all of them. In fact, you can
be quite good in Excel and in modeling by just knowing about
50 or so. The list below presents the 36 functions that I would
consider important to have in your basic repertoire. The list that
follows presents 18 others that would be helpful to know as part
of a more advanced skill set.
63



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




Basic Functions to Know
u Logical functions: IF, TRUE, FALSE, AND, OR. Theses are

functions that allow some measure of decision-making
ability in your formulas.
u Math and trigonometry functions: INT, MOD, ROUND,

SUM, SUMIF. These are for working with numbers.
u Statistical functions: AVERAGE, COUNT, COUNTA, MIN,

MAX. These are for combinations of counting and
summing.
u Lookup and reference functions: CHOOSE, HLOOKUP,

INDEX, MATCH, OFFSET, VLOOKUP. These are for ways
of looking up data.
u Date and time functions: DATA, DAY, MONTH, NOW,

YEAR. These are for specifying dates and time intervals.
u Information functions: ISERROR, ISNUMBER, ISTEXT,

ISBLANK. These are for finding out different types of
information in cells.
u Text functions. LEFT, LEN, MID, RIGHT. These are for

working with text.
u Financial functions: IRR, NPV. These are for calculating

returns and net present value.

More Advanced Functions to Know
u Math and trigonometry functions: ABS, CEILING, FLOOR,

ROUNDUP, ROUNDDOWN, SUMIF, SUMPRODUCT
u Statistical functions: COUNTIF

u Lookup and reference functions: INDIRECT

u Date and time functions: DAYS360

u Text functions. LOWER, PROPER, TEXT, TRIM, UPPER,

VALUE
u Financial functions: XIRR, XNPV




ARGUMENTS IN FUNCTIONS
Anytime you use these functions, you will write them start-
ing with an equal (¼) sign, followed by the name of the




TLFeBOOK
Your Model-Building Toolbox: Functions 65




function you want to use, and then a set of parentheses. Within
the parentheses, you need to specify the types of informa-
tion, called arguments, that the function needs. Some func-
tions, like ¼ NOW(), which returns the current time setting,
do not need any arguments, but you will still need to type in
the parentheses. Others may need one, two, or more arguments.
Some take optional arguments. You can enter them or leave
them out.
Arguments can be:
A cell or range reference, or multiple references:
u

SUM(A1:A10) or SUM(A1:A10,A20,B30)
A whole column: COUNT(A:A)
u

A whole row: MAX(20:20)
u

A range name: AVERAGE(Revenues)
u

Another formula or expression: SUM(250/3, 12*34.5)
u

Other functions: SUM(AVERAGE(C1:C10),
u

AVERAGE(H1:H10)
Optional with a marker: OFFSET(B1,0,2) or OFFSET(B1,,2).
u

The space between the double commas is where the
argument ˜˜0™™ has been left out.
Optional without a marker: HLOOKUP(D5,B2:B3,2) or
u

HLOOKUP(D5,B2:B3,2,TRUE)
Or any combination of the above
u


A time-saving note: When you enter an argument that is a
range reference (e.g., A1:A10), you can use the period (.) as the
separator. Excel will automatically convert that to the colon (:). In
this way, you only have to press one key, rather than two (the
Shift and semicolon keys to get to the colon).



LET™S START
Now that you have some background in how functions work,
let™s go through the list. Because the purpose of this chapter is
to have you become familiar with the functions in the context of
their use, the following is organized a little differently from the




TLFeBOOK
Chapter 5
66




list of categories that Excel has. We will talk about them in the
following way:
The starting point: IF
1.
Alternatives to IF
2.
Functions for adding
3.
Functions for counting
4.
Functions for dates
5.
Functions for looking up data
6.
Dealing with errors
7.
Other functions to know
8.



THE STARTING POINT: IF
The IF statement is the ˜˜granddaddy™™ function in formulas. It
makes the spreadsheet more than just a calculator because IF
allows ˜˜what-if™™ scenarios: different results can be shown
based on different conditions that have been set.
It has three arguments, and its syntax is as follows:

<<

. 12
( 62 .)



>>