Excel Nested If Example

Prof. Yitz Rosenthal 1

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.

2

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 3

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).

4 =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.

Analysis of formula

5

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 … 6

Breakdown of formula

2 parameters for OR

(the 2 nd parameter is an AND function call that has 3 parameters)

3 parameters for inner If

(The 1 st parameter is an OR function call, the 3 rd parameter is another IF function call) 7 =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

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 … 8

Outer If

Analysis of Formula

9 ) =IF( OR( ) B3>=10000, AND( C3="CT", D3=1, B3>=5000 ), 5000*D3, IF( OR(

1st OR parameter 2nd OR parameter 3 parameters for AND 1st parameter of inner If (determines who gets 2nd highest bonus)

C3="NY", C3="NJ"

2 parameters for OR

), 2000, IF(

2nd parameter of inner If (value of 2nd highest bonus) 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) 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) 3rd parameter of inner If (determines what someone gets who didn't get first 2 bonuses)

C3="CT", 1000, 0

3 parameters for inner-most If

) )