1 / 17

CS105 Lab 7 – Excel: The IF Function

CS105 Lab 7 – Excel: The IF Function. Announcements MP3 is due on Saturday, Oct. 13 th at 11:50am . You will lose 15 points if you do not submit MP3 properly. Midterm 1 scores are available in Compass. You have until Saturday, Oct. 13 th to challenge questions on the exam.

rhys
Download Presentation

CS105 Lab 7 – Excel: The IF Function

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. CS105 Lab 7 – Excel: The IF Function • Announcements • MP3 is due on Saturday, Oct. 13th at 11:50am. You will lose 15 points if you do not submit MP3 properly. • Midterm 1 scores are available in Compass. You have until Saturday, Oct. 13th to challenge questions on the exam. • Pick up your feedback form for Midterm 1 today. You can view the Midterm 1 exam test forms in Compass. (the forms were printed before the re-grade)

  2. Lab Objectives • Learn the IF function. • Learn to use nested IF. • Learn to reference other worksheets in Excel. • Learn to use Lookup Tables Go to the course website and download the Excel Worksheet for Lab 7. http://www.cs.uiuc.edu/class/cs105

  3. A “real world” Scenario… • This spreadsheet contains sales data for a tea manufacturing company which sells 3 different brands of tea. • Let’s find out how much of the revenue comes from each product line. • We’ll use the IF function to do this.

  4. Result for False Condition False Condition True Result for True Condition The IF Function Flowchart

  5. How Does IF Work in Excel? • Recall from lecture how the formula =IF(condition, result1, result2) works. • Excel looks at the condition. If it is true,result1 is returned. If it is false,result2 is returned. • The condition must be something that is either true or false; that is, the condition must be BOOLEAN.

  6. Setting Up Our IF Function • Our product names are in column A. • If a cell in column A matches cell E2 (Lively Lemon Tea), we want toput its revenue value (column D) in the Sales column for Lively Lemon Tea(column E). Otherwise we want to put 0 in that column. • How can we do this with IF?

  7. Flowchart for Cell E3 Display 0 Product is Lively Lemon Tea False True Display revenue

  8. The Formula for Cell E3 • =IF($A3=E$2, $D3, 0) • Double-click on the fill handle to fill the column for “Lively Lemon Tea.” • Use the fill handle to copy the formula to cells F3 and G3. • Double-click on the fill handle in F3 and G3 to fill columns F and G.

  9. Nested IF • You can have an IF function inside another IF function. • If “lab is cancelled” then “continue sleeping”, otherwise if “I’m not too sleepy” then “go to lab”, otherwise “continue sleeping” • Nested If is handy if you have more than one condition to test.

  10. False Result for False Condition 1 Condition 1 True Result for True Condition 1 and False Condition 2 False Condition 2 True Result for True Condition 1 and True Condition 2 The Nested IF Flowchart

  11. Calculating Eastern Region Sales • Suppose we want to compute the total sales for each product in the East region. • If column C contains the value “East”, then if the product name (in column A) matches values in row 2, fill the appropriate revenue in column H, I, J. • Otherwise, we want to fill in 0.

  12. The Formula for Cell H3 • =IF($C3<>"East",0,IF($A3=H$2,$D3,0)) • Double-click on the fill handle to fill the column for “Lively Lemon Tea.” • Use the fill handle to copy the formula to cells I3 and J3. • Double-click on the fill handle to fill columns I and J.

  13. AND, OR, and NOT in Excel • As in SQL, Excel has the boolean functions AND, OR, and NOT. • =AND(param1, param2). Both param1 and param2 must be TRUE for the function to return TRUE. • =OR(param1, param2). One or both of param1 and param2 must be TRUE for the function to return TRUE. • =NOT(param). Returns the logical opposite of param.

  14. Using AND to Check Two Conditions • There is an alternative way to write the IF statement in cell H3. We want the revenue to be displayed in H3 if the product is “Lively Lemon Tea” AND the region is “East”: =IF( AND( $C3="East", $A3=H$2), $D3, 0)

  15. Working with Multiple Worksheets Suppose we want to calculate the total tea sales… • Go to the ‘Statistics’ worksheet. • In cell A8, put a formula to add up the range E3:E26 of the ‘Tea Data’ worksheet. The formula is =SUM(‘Tea Data’!E3:E26) • Use the fill handle to copy the formula to B8 and C8. • Compute the grand total of tea sales in cell D8.

  16. Table Lookups In this course, we use two Excel functions for doing table lookups: VLOOKUP and HLOOKUP. How might we use the VLOOKUP function to rate tea sales? • The lookup table is in range D30:E33 =vlookup(value, table, col_index) • In cell K3, use VLOOKUP to compute a rating for the sales amount in cell D3. Copy this formula to cells K4:26.

  17. What you should know? • The formula • =IF($A3=E$2, $D3, 0) • =IF($C3=“East”, IF( $A3 = H$2, $D3, 0), 0) • =IF( AND( $C3="East", $A3=H$2), $D3, 0) • =SUM(‘Tea Data’!E3:E26) • =VLOOKUP(D3,$D$31:$E$34,2) •  Let Excel do all the hard work for you !

More Related