ñòð. 12 |

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 |