1 / 22

Mon Week 9

Mon Week 9. Excel Alice Project Options: Project of the Stars Submit by Wed 11:59 Week 9, also submit questions RE-SUBMIT for regular deadline Regular Submit by Sunday 11:59 of START of week 10 (4/11) Submit reflections one day later ( shh …)

riley-baldwin
Download Presentation

Mon Week 9

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. Mon Week 9 • Excel • Alice Project Options: • Project of the Stars • Submit by Wed 11:59 Week 9, also submit questions • RE-SUBMIT for regular deadline • Regular • Submit by Sunday 11:59 of START of week 10 (4/11) • Submit reflections one day later (shh…) • Week 10: Final Tech and Society (due Thursday 11:59) • Up now, let’s take a look

  2. What is a filter? • A means by which you can restrict the data shown in a table • A means by which you can sort the data stored in a table • A means by which you can remove (e.g. delete) the data in a table • A means by which you can graph the data in a table

  3. When might you choose to lock a cell? • When you want to make sure the value is within a certain range • When you want to make sure the value is of a certain type (e.g. Number, Text, etc.) • When you want to make sure the cell cannot be edited by others

  4. Starting with this, with sort below

  5. In Excel, a relative reference (no $) (compared to an absolute reference (with $))… • Keeps the same row, column reference when you copy it into another cell • Changes the row, column reference when you copy it into another cell, based on the value in the original cell • Changes the row, column reference when you copy it into another cell, based on the location of the new cell compared to the old one

  6. Absolute Addressing is useful for • When you want to always reference the same column, no matter where you copy it • When you want to always reference the same row, no matter where you copy it • When you always want to reference the same cell, no matter where you copy it • More than one of the above is true

  7. Suppose I have two classes grades, in different sheets • I find the average in the first class (Sheet1: B5) • Then I’ll copy that over to (Sheet2: B4) so I’ll have the average grade for that class as well

  8. What is in B4? D. Excel gives an error E. None of the above • 39 • 50 • 78

  9. Which of the following would cause a circular reference if I entered it in B2 and dragged it across row 2? • =B1+B3 • =A2+C2 • Neither of those • Both of those

  10. Assume this function is put into I1 and then copied down to I4, =IF(H1="OK",G1+$F$1,G1-F1) • What is in Cell I3? • 2 • 5 • 9 • 15

  11. Assume this function is put into I1 and then copied down to I4, =IF(H1="OK",G1+$F$1,G1-F1) • What is in Cell I4? • 2 • 5 • 12 • 15

  12. Assigning points with If commands

  13. What is the correct formula to use • For Question 1 • D was worth 2 points • C was worth 1 point • Others worth 0 points • =IF(B3=“D”,2), IF(B3=“C”,1,0)) • =IF(B3=“D”,2,IF(B3=“C”,1,0)) • =IF(B3=“C”1,IF(B3=“D”,2,0)) • More than one of the above • None of the above

  14. Match your PID (from scantron) to name (from classlist) • In one sheet, I have the midterm scanned data • I’ve used if statements to assign points per question and summed them to get exam points • In another sheet I have the downloaded class roster • (which usually has some people who didn’t take the exam – not the same number of rows) • It has PID and Name on each row • Goal: Add a column in midterm sheet with the matching name for each student

  15. What commands would you use to fill A3? • An IF command • An IF command with nested IFs • A VLOOKUP command • A VLOOKUP command with an IF nested in it • A COUNTA function

  16. To calculate A3= VLOOKUP(B3,classList!A$1:B$572,2,TRUE) For all items_in_classList_ColA one at a time If item_in_classList_ColA == midterm!B3 midterm!A3’s value is classList!B(index) else Do Nothing

  17. Chapter 2: Working with Large Worksheets and Tables • A lot of this material is just “hands on” • How do I do that in Excel • You can do that in Excel? • Figure it out now… you won’t remember it unless you use it regularly • Which you won’t • Google “How do I do XXX in Excel 2010” • Critical: • If you didn’t have more data than fits on the screen, you probably wouldn’t bother with Excel…

  18. Things Alice prepared you to do • “Debug” how to freeze rows/columns • Who can remember when you highlight say row 5 and select freeze panes • Will it keep rows 1-4 always visible or 1-5? • Try one, observe, know what to try next if it doesn’t go as you want it… • Lock cells: • Boolean condition attached to cell: property – on or off (Alice had properties too!) • Abstraction (breaking big problems into smaller ones) • Excel file -> worksheets

  19. Find and Replace • Question 32 was tricky. Anyone who got it wrong (0 points) should actually get 0.5 points Not really!

  20. Excel’s find and replace command involves use of which of the following computing concepts?

  21. Which of the following Excel features involve conditional (if statement) actions? • Average ( =Average(A1:A30)) • Sorting Data (=Sort(A1:A30)) • AutoFilters (only show freshmen, female flute players) • More than one of the above

More Related