1 / 9

Excel Nested If Example

Excel Nested If Example. Prof. Yitz Rosenthal. 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.

una
Download Presentation

Excel Nested If Example

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  9. Outer If Analysis of Formula 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 =IF( OR( B3>=10000, AND( C3="CT", D3=1, B3>=5000 ) ), 5000*D3, IF( OR( C3="NY", C3="NJ" ), 2000, IF( C3="CT", 1000, 0 ) ) ) 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

More Related