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

your IF statements.

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.