<<

. 13
( 62 .)



>>

=IF(ThisConditionIsTrue,DoThis,ElseDoThis)

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

<<

. 13
( 62 .)



>>