Excel%20Nested%20If%20Example

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(B2gt10000,AND(C2"CT",D21,B2gt5000)),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(The 1st parameter is
an OR function call, the 3rd parameter is another
IF function call)
2 parameters for OR(the 2nd parameter is an AND
function call that has 3 parameters)

• IF(OR(B2gt10000,AND(C2"CT",D21,B2gt5000)),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

9
Analysis of Formula
Outer If

• IF(
• OR(
• B3gt10000,
• AND(
• C3"CT",
• D31,
• B3gt5000
• )
• ),
• 5000D3,
• IF(
• OR(
• C3"NY",
• C3"NJ"
• ),
• 2000,
• IF(
• C3"CT",
• 1000,

First parameter of outer If (determines who gets
highest bonus. If either parameter in the OR is
TRUE the salesman qualifies for the highest bonus)
1st OR parameter
2nd OR parameter
3 parameters for AND
2nd parameter of outer If (amount of highest
bonus)
3rd parameter of outer If (determines what
someone gets if they didn't get the highest bonus)
1st parameter of inner If (determines who gets
2nd highest bonus)
2 parameters for OR
2nd parameter of inner If (value of 2nd highest
bonus)
3rd parameter of inner If (determines what
someone gets who didn't get first 2 bonuses)
3 parameters for inner-most If