Lecture # 8 Spreadsheets II: Numeric Functions and Data Management

1. Lecture # 8 Spreadsheets II: Numeric Functions and Data Management

2. Today Questions: From notes/reading/life? Cool spreadsheet functions you discovered? 1. Introduce: Can spreadsheets help with my credit problems? 2. Explain: Numeric Functions: Logical Functions, Branching Data Management: Indexing, Look-Up, Charts 3. Demo: Spreadsheet Magic – follow along on laptop 4. Practice: Your Group solves a problem with a spreadsheet 5. Evaluate: Share and evaluate your solution • Re-practice: Calculate monthly payments with a spreadsheet Understand: What is the “Program” or Algorithm behind the Scenes that makes the function work? Do Homework # 3

3. Today Questions: From notes/reading/life? Cool spreadsheet functions you discovered? 1. Introduce: Can spreadsheets help with my credit problems? 2. Explain: Numeric Functions: Logical Functions, Branching Data Management: Indexing, Look-Up, Charts 3. Demo: Spreadsheet Magic – follow along on laptop 4. Practice: Your Group solves a problem with a spreadsheet 5. Evaluate: Share and evaluate your solution • Re-practice: Calculate monthly payments with a spreadsheet Understand: What is the “Program” or Algorithm behind the Scenes that makes the function work? Do Homework # 3

4. Today Questions: From notes/reading/life? Cool spreadsheet functions you discovered? 1. Introduce: Can spreadsheets help with my credit problems? 2. Explain: Numeric Functions: Logical Functions, Branching Data Management: Indexing, Look-Up, Charts 3. Demo: Spreadsheet Magic – follow along on laptop 4. Practice: Your Group solves a problem with a spreadsheet 5. Evaluate: Share and evaluate your solution • Re-practice: Calculate monthly payments with a spreadsheet Understand: What is the “Program” or Algorithm behind the Scenes that makes the function work? Do Homework # 3

5. Today Questions: From notes/reading/life? Cool spreadsheet functions you discovered? 1. Introduce: Can spreadsheets help with my credit problems? 2. Explain: Numeric Functions: Logical Functions, Branching Data Management: Indexing, Look-Up, Charts 3. Demo: Spreadsheet Magic – follow along on laptop 4. Practice: Your Group solves a problem with a spreadsheet 5. Evaluate: Share and evaluate your solution • Re-practice: Calculate monthly payments with a spreadsheet Understand: What is the “Program” or Algorithm behind the Scenes that makes the function work? Do Homework # 3

6. Spreadsheet Numeric Functions

7. Functions • Simple functions • Array functions • IF - logical functions

8. Function notation • A function is a piece of computation that takes 1 or more arguments (data) and returns a value • Function( arg1, arg2, arg3 ) • Sine(90) • Max(100,200,120,-7) • Sqrt( min(7+4, 9) )

9. Using functions

10. Using functions

11. Finding functions

12. Finding functions

13. Finding functions

14. Finding functions

15. Finding functions

16. Finding functions

17. Functions • Simple functions • Array functions • IF - logical functions

18. Sum

19. Count

20. Average

21. Other array functions • Min • Max • Stdev - standard deviation • Median • Product - multiply them all together

22. Functions • Simple functions • Array functions • IF - logical functions

23. If – Logical Functions and Branching • If ( test, trueVal, falseVal)

24. AND

25. IF with AND

26. Review • Functions - name with arguments • Finding functions • Array functions • Sum(B6:D32) • Average(F14:R75) • IF - AND - OR

27. Today Questions: From notes/reading/life? Cool spreadsheet functions you discovered? 1. Introduce: Can spreadsheets help with my credit problems? 2. Explain: Numeric Functions: Logical Functions, Branching Data Management: Indexing, Look-Up, Charts 3. Demo: Spreadsheet Magic – follow along on laptop 4. Practice: Your Group solves a problem with a spreadsheet 5. Evaluate: Share and evaluate your solution • Re-practice: Calculate monthly payments with a spreadsheet Understand: What is the “Program” or Algorithm behind the Scenes that makes the function work? Do Homework # 3

28. Spreadsheet Data Management

29. Data management • Index/Lookup functions • Sorting • Charts

30. Index Demo

31. Today Questions: From notes/reading/life? Cool spreadsheet functions you discovered? 1. Introduce: Can spreadsheets help with my credit problems? 2. Explain: Numeric Functions: Logical Functions, Branching Data Management: Indexing, Look-Up, Charts 3. Demo: Spreadsheet Magic – follow along on laptop 4. Practice: Your Group solves a problem with a spreadsheet 5. Evaluate: Share and evaluate your solution • Re-practice: Calculate monthly payments with a spreadsheet Understand: What is the “Program” or Algorithm behind the Scenes that makes the function work? Do Homework # 3

32. Group Exercise: What Day were You Born? • Using the functions Index and Weekday, discover • which day (Sunday – Saturday) you were born. • Do this for each member of your group. • Which day of the week did the 9/11/2001 attack • occur? • You have 10 minutes

33. Index function • DayNames = Sheet[C2:C8] • DayNumber = WEEKDAY(B2) • Sheet[B3] = DayNames[DayNumber,1]

34. Match function

35. Match Demo

36. Lookup Function

37. Lookup Function How much time? 5, 50, 50000

38. Lookup Demo

39. Sorting Ascending - smallest to largest item[i]<=item[i+1] Descending - largest to smallest item[i]>=item[i+1]

40. Sort Demo • Simple sort buttons • Sort menu item • Data -> Sort • Ward list • Name, apartment, dates last month

41. Charts

42. Today Questions: From notes/reading/life? Cool spreadsheet functions you discovered? 1. Introduce: Can spreadsheets help with my credit problems? 2. Explain: Numeric Functions: Logical Functions, Branching Data Management: Indexing, Look-Up, Charts 3. Demo: Spreadsheet Magic – follow along on laptop 4. Practice: Your Group solves a problem with a spreadsheet 5. Evaluate: Share and evaluate your solution • Re-practice: Calculate monthly payments with a spreadsheet Understand: What is the “Program” or Algorithm behind the Scenes that makes the function work? Do Homework # 3

43. Homework #3: What is Your Payment? • You want to buy a car for $10,000 and you want to pay it off in 3 years. You can get a loan for 4% • annual interest. Use a spreadsheet function to calculate your monthly payment. • Let’s say that the maximum monthly payment you • can afford is $200 a month. How long will it take • you to pay off the car? • Let’s say you are stuck with 4% annual interest and • 3 years. How expensive of a car can you afford?

44. Today Questions: From notes/reading/life? Cool spreadsheet functions you discovered? 1. Introduce: Can spreadsheets help with my credit problems? 2. Explain: Numeric Functions: Logical Functions, Branching Data Management: Indexing, Look-Up, Charts 3. Demo: Spreadsheet Magic – follow along on laptop 4. Practice: Your Group solves a problem with a spreadsheet 5. Evaluate: Share and evaluate your solution • Re-practice: Calculate monthly payments with a spreadsheet Understand: What is the “Program” or Algorithm behind the Scenes that makes the function work? Do Homework # 3

45. Spreadsheet “Programs” • What is the “Program” behind the scenes that makes a spreadsheet function work?

46. Sum program Sum=0; for (each row R) { for (each column C in row R) { Sum=Sum+Sheet[R, C]; } }

47. Sum program Sum=0; for (each row R) { for (each column C in row R) { Sum=Sum+Sheet[R, C]; } }

48. Sum program Sum=0; for (each row R) { for (each column C in row R) { Sum=Sum+Sheet[R, C]; } }

49. Sum program Sum=0; for (each row R) { for (each column C in row R) { Sum=Sum+Sheet[R, C]; } }

50. Sum program Sum=0; for (each row R) { for (each column C in row R) { Sum=Sum+Sheet[R, C]; } }