<<

. 14
( 62 .)



>>

between A10 or 0. Thus, if A10 is a negative number, 0 is the
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Þ,

<<

. 14
( 62 .)



>>