1. Mathematics Cookies 2010 Term 2 SabbaticalHwa Chong Institution

2. What you will be doing in this course…

3. Outline of course

4. End Product

5. End Product

6. A few pointers… • Thumbdrives cannot be used in the computer lab • Please save your work and upload it in the Internet (e.g. your email, Google Docs or Acrobat.com) • GeoGebra (you may need to use it later) has a new free web version at http://www.geogebra.org/webstart/geogebra.html

7. MS Excel 2010 Term 2 Sabbatical – Mathematics Cookies Hwa Chong Institution

8. What you will learn

9. Why Excel?

10. What can be done with Excel?

11. Do-It-Together Lab demonstration 1

12. Lab Demonstration 1 Think about how to go about doing this excel applet… What components does it have?

13. Step 1 • Set up the display and resize cells • Think: • How to prevent cells from being selected after typing the plus sign “+”? • How to resize cells quickly and effectively?

14. Step 2 • Create a scrollbar for the values m and c • View> Toolbars > Forms> Scrollbar • Draw under m and c (i.e. cells B1 and D1)

15. Step 3 • Set values and link the scrollbars to the cells • Scrollbar [Right-click] > Format Control > Control • Set Min=0, Max=10 and Increment=1 • Cell link to cell you right-clicked so that the value will appear in the cell. Do the same for the other scrollbar.

16. Step 4a • Set up table of values • Set range of x from -10 to 10 • Think: • Is there a faster method to set the x values?

17. Step 4b • For the values of y, it should be y=mx+c • For cell B6, it will be $B$1*A6 + $E$1 • ($B$1 is m, A6 in this case is x and c is E1) • Think: • What is the difference between naming “$B$1” and “B1”? • Is there a faster method to set the y values?

18. Step 5 • Draw the graph using Chart Wizard • Insert> Chart > XY Scatter>2ndSubtype > Next > Select Data Range > Next > Finish

19. Questions to Ponder… • Think of these questions when you improve on it for lab exercise 1… • The y-axis range keeps changing when you scroll the m values. How can I fix that? • Improve the design of the applet. • Remove the gridlines. • Hide the range of x and y values. • How can I set negative values for m and c? • How to add in rectangles with text which will appear only on hovering the mouse above?

20. My Sample

21. Overview: • Set up display • Create a scrollbar for the values m and c: View > Toolbars > Forms > Scrollbar (For Excel 2007: Developer > Insert) • Set values and link the scrollbars to the cells: Scrollbar [Right-click] > Format Control > Control • Set up table of values: • X: Set range of x from -10 to 10 • Y (=mx+c): e.g. Cell B6: $B$1*A6 + $E$1 • Draw the graph: Insert > Chart > XY Scatter > 2nd Subtype > Next > Select Data Range > Next > Finish(For Excel 2007: Insert > Scatter > 2nd Subtype > Next > Select Data Range > Next > Finish) • Text: How does the graph of the function y = Ax + B compare with the graph of the function y = x as we change the values of the variables A and B? Improve on your Excel Applet: 8.30am – 10am Lab Exercise 1

22. Step 1 - Tips • Prevent cells from being selected after typing the plus sign “+” • Press “Enter” after typing into the cell • Resize cells quickly and effectively • Double click between the typed cell column and the one to the right when you see the resize cursor

23. Step 4a - Tips • Is there a faster method to set the x values? • Instead of keying into individual cells, you can key in “-10”, “-9”, “-8” and drag the “+” autofill cursor when hovered over the bottom-right corner of the cell. • Excel will auto fill the cells according to the pattern. • Stop dragging once you reach “10”.

24. Step 4b - Tips • What is the difference between naming “$B$1” and “B1”? • If you name the cell as (e.g.) “=B1”, it may autofill “=B2”, “=B3” and so on. • Typing “$B$1” will not change its value when auto-filling.

25. AOT • Fix x & y axis: Right click on axis > Range • Insert new x=y line: Format Chart > New Series • Set negative values for m & c • Additional: Rectangles with text which will appear only on hovering the mouse above: comments • How to change colour of scrollbar?

26. 10am-11am Break time!

27. Lab Demonstration 2

28. What is this applet? • Allows user to solve simultaneous equations • Requirements: • User can input values for A, B, C, D, E and F. (Scrollbars not needed) • Applet can display the types of lines (parallel, intersecting, same line etc…) • If intersecting lines, applet can display the point of intersection

29. Questions to Ponder: How to input the formulas for types of lines and points of intersection?

30. Questions to Ponder: How to input the formulas for types of lines and points of intersection? Use the IF loop: =IF (A = B, “value if true”, “value if false”)

31. Overview: • Set up display • Ax+By1=C • Dx+Ey2=F • Set x values (-20 to 20) • Set y values • y1=C/B-Ax/B • y2=F/E-Dx/E • (Press F4 to change cell name to dollar sign) Do it yourself! Lab Exercise 2

32. Determine intersecting, parallel or same lines • Test for parallel lines: • A=D • Test for same lines: • c1=c2 • If not parallel lines, they are intersecting lines. • Calculate intersecting point • Solve the 2 SLE for x & y • X=(FB-CE)/(DB-AE) or vice versa • Y=(C-Ax)/B • Point’s coordinates are x & y values (remember to specify no. of sol. for parallel & same lines) • If you are done, think about how to test for perpendicular lines (will not go through though) Do it yourself! Lab Exercise 2

33. Reflections End of Day 1

34. Group Applet Project Design Day 2

35. Agenda for today • (Re)visiting Excel tricks & shortcuts • Group Applet Project Design Briefing • Deciding on Applet • Seat in your own groups • Designing Applet • GeoGebra (if time permits)

36. Recap • Create a scrollbar • View > Toolbars > Forms > Scrollbar • For Excel 2007 & 2010: Start Logo > Options > Customise > Developer > Controls > Insert (Excel 2007 & 2010 forms are Developer Forms, not the toolbar control forms) • Set values and link scrollbars to cells • Right-click on scrollbar > Format Control > Control • Draw a graph • Insert > Chart > XY Scatter > [Choose options] • For Excel 2010: Insert > [Choose Graph] (Then edit the graph by right-clicking on components)

37. Recap • Autofill • Drag the plus-sign cursor at the bottom-right corner of the cell • Resize columns and rows of worksheet • Double-click on the double-arrowed line cursor between 2 rows or columns • Comments • Insert > Comment • For Excel 2007 & 2010: Review > New Comment

38. Recap • The IF Statement • =IF (argument, “statement if true”, “statement if false”) • IF Loop (red part is if 1st argument is true): • =IF(1st argument, IF(2nd argument, “statement if 2nd argument is true”, “statement if 2nd argument is false”), “statement if 1st argument is false) • Change cell name to dollar-sign cell name by pressing [F4] • A more professional way to hide cells • Right click on selected cells > Format Cells > Protection > Hidden

39. Group Applet Project Design • Choose a topic that can be taught with the help of an excel applet

40. Requirements

41. Samples • Quadratic Equations • Solving Cubic Equations via Depression • Solving Simultaneous Equations • 1 quadratic & 1 linear • Trigonometrical Functions • Sine Functions • Cosine Functions • Tangent Functions • Combined Functions with absolute value

42. Samples • Drawing a line from 2 points • Calculating gradient, y-intercept and equation • Reflection of lines & triangles • Drawing a triangle from 3 points • Calculating gradient, y-intercept and equation of lines formed • Calculating area of triangle formed

43. For more ideas… • Wolfram Mathematica Demonstrations Project • http://demonstrations.wolfram.com/ • Is not Excel but may give you some ideas of what to do (if you want to do something more challenging) • The Excel Applet samples have been compiled into a view-only file available for download at http://2010mathcookies.wikispaces.com/ • Please do not plagiarise… 

44. Appendix: excel shortcuts • Merge rows & columns • For Excel 2007 & 2010: Home > Alignment > Merge & Center • View full screen • View > Full Screen (no keyboard shortcut) • Split Excel workbook view into two (works in Word also) • Grab the rectangle on the top-right corner between the formula bar & column labels, on top of the scroll bar on the right • Drag it down to see 2 views • Can be used, e.g. to keep the first row (header) always intact on top

45. Appendix: excel shortcuts • Lock workbooks, worksheets & cells • Lock cells • Right click on cell > Format Cell • For Excel 2007 & 2010: Home > Cells > Format > Format Cell (or Lock Cell) • Protect sheets • For Excel 2007 & 2010: Review > Changes > Protect Sheet • Protect entire Excel workbook • For Excel 2007 & 2010: Review > Changes > Protect Workbook

46. 8.30am – 9am Group Discussion Topic for applet Applet Design

47. 9am – 10am Design of Applet

48. 10am – 11am Break time!

49. 11am – 1pm Design of Applet (continued)(Going on to GeoGebra if time permits)

50. Reflections (remember to copy your reflections into the WikiSpaces group workspace for reference for days 1 & 2 when you do your reflections for your final product) End of Day 2