As an example, you can have the condition ˜˜If A1 contains a

number that is not a zero, then show the contents of B1, other-

wise show the contents of C1.™™ This would be written as:

=IF(A1<>0,B1,C1)

Let™s look at this formula carefully and note the following:

Functions are formulas, so you should begin with the

u

equal sign (¼). This in effect tells Excel: ˜˜Get ready to

calculate the following.™™ If you write the formula above

into a cell without the equal sign, Excel will show it as

text, a string of characters that will not calculate.

The formula given will show the contents of the cells B1

u

or C1. We can, however, have the choices occur in the IF

TLFeBOOK

Your Model-Building Toolbox: Functions 67

function itself. We could write:

IF(A1<>0,10,22)

You can put other operators (¼, >, <, <¼, >¼) in the place

u

of the <> sign in the condition. If we want the choices to

show text, we have to enclose those messages in double

quotes:

IF(A1>10,“Sell”,“Hold”)

You do not have to write the IF word, or any other

u

function word, in capitals. It will always appear as

capitals. Of course, any text you want the formula to

show like the ˜˜Sell™™ and ˜˜Hold™™ will show up exactly

as you typed them.

It™s TRUE or FALSE

Internally, Excel actually keeps track of whether something is

true or not. Try this. In an empty spreadsheet, put the number

1 in cell A10. Then, in A11, write just the condition of the IF

statement we have been experimenting with.

= A10=1

Remember to put the ¼ sign at the beginning. We want

Excel to calculate this. A11 will now show

TRUE

Change A10 so that it contains something else: a number

that is not 1, or the text ˜˜1™™ (type an apostrophe and then the

number 1), or make A10 a blank. Then the formula will show

FALSE

These words appear even though you did not type them in.

The words TRUE and FALSE are keywords in Excel. They are

part of Excel™s own vocabulary of words with a distinct meaning

and function in Excel. All the names of functions (IF, SUM, etc.)

TLFeBOOK

Chapter 5

68

are also keywords. To avoid confusion, do not use these key-

words for naming range names.

What this means is that in the IF formula we were using:

= IF(A10=1,B10,C10*12)

Excel™s IF function was testing

= IF(TRUE,B10,C10*12)

What the formula returns is based on whether the test is TRUE

or FALSE.

Booleans: The Value of TRUE or FALSE

More interestingly, TRUE and FALSE have values. TRUE is 1,

and FALSE is 0. This is the basis of something called Boolean

logic, a system of algebraic logic invented by the English math-

ematician George Boole (1815“1864). What can we do with this?

We can use this Boolean logic as another way to create an IF

condition. Here is an example:

=(A10=1)*100

You will need the parentheses around the A10 ¼ 1 to make it

clear to Excel that this is the Boolean unit. Otherwise, it will

simply try to test if A10 is equal to 1 * 100.

Thus, when A10 is 1, the formula will return 100, the result

of 1 * 100, or 100. When A10 is not 1 (it is another number, a

word, or blank), the formula is 0, the result of 0 * 100, or 0.

We™re getting the same result as:

=IF(A10=1,100,0)

Boolean formulas are nice to use when you want to put in a

simple switch to turn a cell™s numeric contents on and off. If you

want to mimic the conditional switch of DoThis, ElseDoThis of an

IF statement, you have to string two Booleans together. The IF

statement:

IF(A1<>0,10,22)

TLFeBOOK

Your Model-Building Toolbox: Functions 69

would look like this in a Boolean:

=10*(A1<>0)+22*(A1=0)

The only caveat with Booleans is that they are so compact

that somebody unfamiliar with them can be totally confused.

In Most Cases, You Can Drop the ˜˜< >()™™

One of the most common IF statement tests is to check whether

the test cell is a zero or not. This formula, for example, tests

whether A10 is not a zero. If it is not, then use it as the denomi-

nator in the fraction. If it is, then just return a 0. We do not want

to calculate the ratio if A10 is 0, because dividing by zero will

lead to an error:

=IF(A10<>0,A5/A10,0)

You can simplify by leaving out the ˜˜<>0™™ portion:

=IF(A10,A5/A10,0)

This may look strange at first, but you will find that you

work faster, not just because you are saving the keystrokes, but

also because the internal voice in your brain no longer says ˜˜is

not zero™™ as you check your formulas.

A word of caution: Dropping this will have no effect for

basic or even nested IF statements (see the section below), but

if you are testing multiple conditions through the use of AND or

OR, you should continue to include the ˜˜<>0™™.

Nested IF Statements

We have been doing only one thing so far. This is to look at an IF

statement that looks at whether one condition is true or not, which

leads to two choices, do this or do that.

There will be occasions when you want to look at more

than one condition that leads to more than two choices. In

other words, if one condition is true, do this; otherwise, if a

second condition is true, then do that; otherwise, do a third thing.

TLFeBOOK

Chapter 5

70

We can build this kind of formula using the basic form as a

start. Essentially, the do this or do that can be other IF statements.

So let™s write a formula that says if A10 ¼ 1, then show the

number 100; otherwise, if A10 ¼ 2, then show the number 200;

otherwise (i.e., if A10 is not 1 or is not 2), show the number 999.

=IF(A10=1,100,IF(A10=2,200,999))

We can keep slipping IF statements into this kind of formula

in a process called ˜˜nesting,™™ and the result is called a nested IF

statement.

=IF(A10=1,IF(D10=20,100,500),IF(B10=5,200,

IF(C10=10,999,300)))

Excel has a limit of seven nesting levels, but I think you will

find the before you reach that final level, your own brain will

have reached its limit of being able to keep track of which IF

goes with which this or that. The limit of seven nesting levels

refers to any combination of functions, not just IF.

Be Careful: A Zero May Not Be a Zero

In cases where the condition of an IF formula is whether a cell is

0 or not, be aware that sometimes a cell may look like it contains

a 0, but does not. Excel translates numbers from the base 10

numbers to the hexadecimal (base 16) code of the computer for

its calculations and then retranslates that back again. A discre-

pancy may result. This discrepancy is miniscule (you may see a

number like 0.00000045, for example) and generally will not have

a material effect on calculations. It can, however, have a great

effect on an IF condition. If you set a formula such as:

=IF(D17=0,A10,B10)

that small discrepancy in D7 will make the D17 ¼ 0 condition

return a FALSE. If you do need to use this test, do a ROUND

TLFeBOOK

Your Model-Building Toolbox: Functions 71

function to 1 or 2 decimal places, thus:

= IF(ROUND(D17,2)=0,A10,B10)

ALTERNATIVES TO IF

The following functions are full-fledged functions in themselves,

but they are also useful as alternatives to the IF function. They

are the following:

MIN

u

MAX

u

CHOOSE

u

OFFSET

u

AND

u

OR

u

MAX and MIN

MAX is a function that shows the greatest value among its argu-

ments. MIN shows the smallest value.

To show results that are always above zero, you can write it

with an IF statement:

=IF(A10>0,A10,0)

This means that if A10 is over 0, then show it, otherwise (if

it is a zero or negative number), just show a zero. You can write

it with the formula that is more efficient, like this:

= MAX(A10,0)

This reads: Show the maximum of or the greater number