1
Excel Nested If Example
Prof. Yitz Rosenthal
2
Sample Spreadsheet with Raw Data
• The following spreadsheet will be used for this presentation.
• We will develop a formula to calculate employee bonuses.
• The formula will be placed in cell E2 and copied to the other
cells in column E.
3
Rules to calculate bonus
• The following rules are used to calculate the
employee bonuses.
– The bonus is $5,000 times the number of years on the
job if either
• the person made at least $10,000 in sales OR
• the person is from CT and they are on the job for exactly one
year and they made at least $5,000 in sales
– If the person didn't qualify for the larger bonus then :
• The bonus is $2,000 for anyone from NY or NJ
• The bonus is $1,000 for anyone from CT
• For people from any other state the bonus is zero
4
Excel Formula to calculate bonus
• Place the following formula in cell E2 to calculate
the bonus for the first employee (see next slide for a
breakdown of the formula).
=IF(OR(B2>=10000,AND(C2="CT",D2=1,B2>=5000)),5000*D2,IF(OR(C2="NY",C2="NJ"),2000,IF(C2="CT",1000,0)))
• Then copy that formula to the rest of the cells in
column E to calculate the bonus for the other
employees.
5
Analysis of formula
6
Analysis
• The following slides attempt to show graphically
how the formula is broken down and constructed.
• The slide identifies the different parameters for the
function calls that make up the larger formula.
• Not every function call is broken down in the
diagram …
7
Breakdown of formula
3 parameters for inner If
2 parameters for OR (The 1st parameter is an OR function call, the 3rd parameter
(the 2 parameter is an AND function call that
nd
is another IF function call)
has 3 parameters)
( ( , ( , ,
=IF OR B2>=10000 AND C2="CT" D2=1 B2>=5000 )),5000*D2,IF( OR(C2="NY",C2="NJ") , 2000 , IF ( C2="CT" , 1000 , 0)))
3 parameters for
inner-most if
3 Parameters for outer if
8
Analysis 2
• The following slide shows a different way of
analyzing the formula.
• In Excel the formula must be written on one line.
However, sometimes it helps to break up the
formula on different lines to visually see where the
nested functions are.
• In the following slide the formula is written on
multiple lines. The parameters for each function of
the larger formula are indented one more level
than the function name.
• See the next slide …
Outer If
Analysis of Formula 9
=IF(
OR( First parameter of outer If
B3>=10000, 1st OR parameter (determines who gets highest
AND( bonus. If either parameter in
C3="CT", 2nd OR parameter the OR is TRUE the salesman
D3=1, 3 parameters for AND
qualifies for the highest bonus)
B3>=5000
)
), 2nd parameter of outer If
5000*D3, (amount of highest bonus)
IF(
1st parameter of inner If (determines who gets 2nd highest bonus) 3rd parameter of outer If
OR(
C3="NY",
(determines what someone gets
C3="NJ" 2 parameters for OR if they didn't get the highest
), bonus)
2000, 2nd parameter of inner If (value of 2nd highest bonus)
IF( 3rd parameter of inner If (determines what someone gets who didn't get first 2 bonuses)
C3="CT",
1000, 3 parameters for inner-most If
0
)
)
)