greater number, and that is the result you will see. If it is posi-

tive, then A10 will be the number displayed.

Likewise, the following are equivalents:

= IF(A10<0,A10,0)

= MIN(A10,0)

TLFeBOOK

Chapter 5

72

Here is the variation if you want to say: Show A10 only if it

is less than zero, but show it as a positive number:

=IF(A10<0,-A10,0) Note the minus sign in front of A10

=-MIN(A10,0) Note the minus sign in front of the MIN

or an alternative using MAX

Note the minus sign in front of A10

=MAX(-A10,0)

MIN and MAX become a much better choice over IF when

there are more elements:

= MAX(A10,B10,C10,0)

or

= MAX(A10:C10,0)

An equivalent IF statement would be

= IF°AND°A10>= B10,A10>= C10,A10>= 0Þ,A10,

IF°AND°B10>= A10,B10>= C10,B10>= 0Þ,B10,

IF°AND°C10>= A10,C10>= B10,C10>= 0Þ,C10,0ÞÞÞ

As you can see, this tortuous formula is horrendously diffi-

cult to write correctly and check, and this is only to test just three

cells against the value 0.

In the case where we want to find the maximum or mini-

mum values of a range or a row, there is no other way but to use

MAX or MIN:

= MAX°A1: G16Þ

= MIN°10:10Þ

TLFeBOOK

Your Model-Building Toolbox: Functions 73

MIN and MAX Together

If you want to show the value only as between 0 and 5, you can

use the two together in the same formula:

= MIN(MAX(A10,0),5)

In this way, any number that is in A10 will be shown only

as a number between 0 and 5. The MAX function will show only

numbers in A10 that are equal to or greater than 0. Potentially,

this could be, say, 27. The MIN will show that result only

between 0 (the bottom limit of the MAX) and 5 (the limit set

by the MIN). So this is a way to limit the number that is being

read elsewhere in the model to only the values between 0 and 5.

Another way is to put a limit on the input cell so that only

this range of values can be entered. You can do this through the

Data Validation feature of Excel. See Chapter 18, ˜˜Bells and

Whistles.™™

MIN and MAX with Negative Numbers

MIN will always return the lowest value, in positive or negative

numbers; MAX likewise returns the highest value.

If the range named TestRange contains 1,2,3, then

= MIN°TestRangeÞ will return 1

= MAX°TestRangeÞ will return 3

Note the results for negative values. If the range named

TestRange contains À1,À2,À3, then

= MIN°TestRangeÞ will return-3

as this is the lowest value

= MAX°TestRangeÞ will return-1

as this is the highest value

Because of this, be careful with using MIN and MAX with

negative numbers if your intent is to find a result that is closest,

or farthest, from zero on the numbers scale.

TLFeBOOK

Chapter 5

74

CHOOSE

CHOOSE is a simple function for selecting an item from among

a list.

= CHOOSE(index_number, choice1, choice2,. . . choice29)

Based on the index number (the first value), CHOOSE

selects from the values listed. The index number cannot be a 0.

The following example is a CHOOSE function that will return one

of the four entries depending on the value entered in cell C10:

= CHOOSE(C10,“Apples”,“Bananas”,

“Cherries”,“Dates”)

This example uses text strings, but the choices listed can be

any of the acceptable forms for function arguments.

If we had written this with IF, it would have looked like this:

IF°C10= 1,“Apples”,IF°C10= 2,“Bananas”,

IF°C10= 3,“Cherries”,“Dates”ÞÞÞ

As you can see, the benefit of CHOOSE is that it is much

more straightforward. The disadvantage is that you have to list

the choices individually because CHOOSE does not work with

ranges. You cannot write CHOOSE(C10,D10:G10), for example.

(You cannot do that with IF either.) Not being able to work

with ranges makes writing CHOOSE rather awkward when

you have upward of 10 choices. The limit for the values listed

is 29; thus, the index number must be between 1 and 29.

To have the option of entering a 0 in order not to select any

value, use the following variation. Since the first position is taken

up by the ˜˜™™, you can only list 28 other options.

= CHOOSE(C10+1,“”,“Apples”,“Bananas”,

“Cherries”,“Dates”)

OFFSET

OFFSET is a kind of CHOOSE that can work with ranges. In fact,

it has to work with ranges so that you cannot pick choices from

TLFeBOOK

Your Model-Building Toolbox: Functions 75

different parts of a worksheet or from different worksheets.

However, it does not have a limit of the number of choices, so

you can actually choose from a lot more choices than CHOOSE.

The syntax is

= OFFSET(Reference,RowsOffset,ColumnOffset)

OFFSET is actually a different ˜˜animal™™ than CHOOSE

because at its core it is a function of finding a cell that is so

many rows and so many columns away from a starting reference

cell. We can use this ability to pick a remote cell as a way to

choose, but it is important to remember this is a far more power-

ful function. Here™s how it works.

The formula

= OFFSET(K10,2,1)

will return the contents of the cell 2 rows down and 1 column to

the right of the reference cell K10, which is to say, the contents of

cell L12. The first number after the reference cell is the number of

rows away from the reference cell. A positive number means the

row is below the reference cell; a negative number means it is

above. The second one is the number of columns away; a positive

number means to the right of the reference cell; a negative

number means to the left.

(In other functions in Excel where you need to define rows

and columns, the system is the same. It is always rows first and

then columns.)

OFFSET(K10,À2,À1), with the negative rows and columns

numbers, will return the contents of the cell 2 rows above and

1 column to the left of the reference cell K10, or cell J8. You can

have a negative row count and a positive column count, and vice

versa.

If we write

OFFSET(K10,0,0)

we get the contents of K10, because the formula is asking for

0 rows and 0 contents away from K10.

TLFeBOOK

Chapter 5

76

Here is a comparison of IF, CHOOSE, and OFFSET. These

are set to work an input toggle in cell A1. All these will return

˜˜Apples™™ if A1 is 1, ˜˜Bananas™™ if it is 2, and so on.

A B C D E F G H

Apples Bananas

1 Cherries Dates

1

2 10

3 =IF(A1=1, B1, IF(A1=2, C1, IF(A1=3, D1, E1)))

4

5 =CHOOSE(A1, B1, C1, D1, E1)

6

7 =OFFSET(A1,0,A1)

With OFFSET, the list of items has to be in a contiguous

range, because the function is simply counting how many cells

away the data cell is from the reference cell. In this example,

there is another important point to note. If we move the list of

items to a vertical range, the IF and CHOOSE references will

automatically change, but we would need to manually adjust

the OFFSET formula:

A B C D E F G H

1 1

2 Apples 10

3 Bananas =IF(A1=1, A2, IF(A1=2, A3, IF(A1=3, A4, A5)))

4 Cherries

5 Dates =CHOOSE(A1, A2, A3, A4, A5)

6

7 =OFFSET(A1,A1,0)

AND

As part of writing the IF statement, you can use the AND func-

tion, which combines the conditions. It is not an alternative to

IF as such, but you could say that it is an alternative to a second

IF. Rather than writing a nested second IF, you could combine

the conditions with one AND, and write just one IF. The AND

syntax is:

AND(condition1,condition2,. . .)

TLFeBOOK

Your Model-Building Toolbox: Functions 77

Notice that the AND precedes everything and the conditions

are enclosed in parentheses. The function will return a TRUE if

all the conditions in the function are true, and it will return a

FALSE if even only one is not true. AND can contain up to 30

conditions.

AND is very helpful if you need to write a formula for the

following, for example: If the employee™s age is 50 or over, and

his age plus length of employment is 70 or over, then he can

retire. In the formula, ˜˜Age™™ and ˜˜Employment™™ are range

names for individual cells containing the relevant data.

= IF°AND°Age>= 50,Age+Employment>=70Þ,