Microsoft Office Tutorials and References
In Depth Information
REVIEW OF EXCEL BASICS
To take the IF argument one step further, assume that the Total Sales Dollars for 2012 depended not only
on the Economic Outlook, but on the Inflation Outlook (High or Low). Say there are two possibilities:
￿
Possibility 1: If the economic outlook is for a Recession and the inflation outlook is High, the
Total Sales Dollars for 2012 will be 30% higher than in 2011.
￿
Possibility 2: For the other three cases (Recession and Low Inflation, Boom and High Inflation, and
Boom and Low Inflation), assume that the Total Sales Dollars will only be 15% higher than in 2011.
The first possibility requires two conditions to be true at the same time: C8=
R
and C9=
H
. You can
include an AND( ) function inside the IF statement to reflect the additional condition as follows:
=IF(AND(\$C\$8=
), B18*130%,B18*115%)
When the test argument uses the AND() function, conditions
R
,\$C\$9=
H
R
and
H
both must be present at the
same time for the statement to use the true result (multiplying last year
s sales by 130%). Any of the other three
outcome combinations will cause the statement to use the false result (multiplying last year
s sales by 115%).
You can also use an OR() function in an IF statement. For example, assume that instead of both
conditions (Recession and High Inflation) having to be present, only one of the two conditions needs to be present
for sales to increase by 30%. In this case, you use the OR() function in the test argument as follows:
=IF(OR(\$C\$8=
R
,\$C\$9=
H
), B18*130%,B18*115%)
) is true, the function will return the true
argument, multiplying the 2011 sales by 130%. If neither of the two conditions is true, then the function will
return the false argument, multiplying the 2011 sales by 115% instead.
In this case, if either of the two conditions (C8=
R
or C9=
H
Using IF Statements Inside IF Statements (Also Called
)
By now you should be familiar with IF statements, but here is a quick review of the syntax:
Nesting Ifs
=IF(test condition, result if test is True, result if test is False)
In the preceding examples, only two courses of action were possible for each of the inputs: Recession or
Boom, High Inflation or Low Inflation, Rental Occupancy High or Low, Bond Money Available or No Bond
Money Available. The tutorial used only two possible outcomes to keep them simple.
However, in the business world, decision support models are frequently based on three or more possible
outcomes. For capital projects and new product launches, you will frequently project financial outcomes
based on three possible scenarios: Most Likely, Worst Case, and Best Case. You can modify the IF statement
by placing another IF statement inside the result argument if the first test is false, creating the ability to
launch two more alternatives from the second IF statement. This is called
Try a simple nested IF statement first: In your thrift shop example, assume that three economic outlooks
are possible: Recession (R), Boom (B), or Stable (S). As before, the 2012 Total Sales Dollars (cell C18) will be
the 2011 Total Sales Dollars increased by some fixed percentage. In a Recession, sales will increase by 30%, in a
Boom they will increase by 15%, and for a Stable Economic Outlook, sales will increase by 22%, which is
roughly midway between the other two percentages. You can
nesting
nest
the IF statement in cell C18 to reflect the
third outcome as follows:
=IF(\$C\$8=
,B18*115%,B18*122%))
Note the added IF statement inside the False value argument. You can break down this statement:
R
,B18*130%,IF(\$C\$8=
B
￿
If the value in cell C8 is
R
, multiply the value in cell B18 by 130%, and enter the result in cell C18.
￿
If the value in cell C8 is not
R
, check whether the value in cell C8 is
B
. If it is, multiply the
value in cell B18 by 115%, and enter the result in cell C18.
￿
If the value in cell C8 is not
B
, multiply the value in cell B18 by 122%, and enter the result in
cell C18.
If you have four or more alternatives, you can keep nesting IF statements inside the false argument for
the outer IF statements. (Excel 2007 and later versions have a limit of 64 levels of nesting in the IF function,
which should take care of every conceivable situation.)
NOTE
The “embedded IFs” in a nested IF statement are not preceded by an equals sign. Only the first IF gets the equals sign.

Search JabSto ::

Custom Search