1 / 23

Wed Week 9

Wed Week 9. 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 …)

vidor
Download Presentation

Wed 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. Wed Week 9 • 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

  2. What’s Left • Excel • RETURN to Alice! • Let’s connect Excel things to Alice! • VLOOKUP columns and Bud frogs • A new solution • This is “new stuff” but ALSO review for the final!

  3. I’ve made a Pivot Chart thusly:

  4. Suppose I play create one like this:

  5. Which table will result?

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

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

  8. Which best describes the processimplemented by VLOOKUP? • Two nested loop tiles • A loop tile with an if statement in it • An if statement with a loop in it • A series of if statements

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

  10. 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…

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

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

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

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

  15. A challenge: Create the Budweiser commercial in 2 lines of code • Previously, with an if statement…

  16. Not good style… Relies on specific names Three method calls to “say” All same EXCEPT different parameter

  17. Index: An indicator of “where” something is • Index of textbook hasentries saying where tolook for specific entries • Lists (also called arrays)in programming also “find” their entries viaan index: 0, 1, 2, 3, …

  18. Alice will give you an object out of a list, if you give it the index • Drag the froggies list over to replace the “frog” object • A list pops up, select ith item from list • Select which item There are 3 items: 0, 1 and 2* * In computing we always count starting at zero

  19. So, these are equivalent given my list like this:

  20. We can create lists of “related” things and loop over them using the index

  21. Counted Loop: The Advanced Version • We want to loop over three things: • But we need access to the “index” of our counted loop (click show complicated) • index (a number) will have the values: 0 first time through loop1 second time through loop2 third time through loop

  22. So which code will make create our “Budweiser commercial”?

More Related