U7 Spreadsheet Operations II (Optional)

1. U7 Spreadsheet Operations II(Optional) Presented by: Law Hing Man (11 Dec 2001)

2. 7.1 Overview of Using Spreadsheet as a Teaching and Learning tool • Spreadsheet is not just a tool for handling marks and statistics. • Spreadsheet can be used in teaching and learning. • By using charts, auto-recalculation and macro, spreadsheet can be used to develop an Interactive Learning Environment (ILE).

3. Step 1 Step 2 7.2 Creating and Formatting Charts Steps to create a chart (Example1) Step1: Select the data to be included in the chart (Including column and row labels) Step2: Click the Chart wizard button (or select Insert -> Chart…)

4. Step3: Select the Chart type and click next.

5. Step4: Change the data range if necessary.

6. Step5: Key in title and axis labels

7. Step6: Select the location of your chart. (either as a new sheet or as an object in an existing worksheet)

8. The changes in each item will interactively change the corresponding portion in the chart.

9. 7.3 Spreadsheet as a “What-if” Analysis Tool • “What-if” What will happen to thatif I change this. • Example 2 Mortgage repayment analysis PMT(r,nper,loan) calculates the periodic payment of a loan where r : Interest rate per period nper : Number of payments loan : The amount of loan

10. variables =-PMT(B2/12,B3*12,B1) By changing different variables, we will get different results. What is the minimum number of years if one can only afford $10000 monthly repayment?

11. Example 3 – Mark Adjustment =(B2+C2)/2 Copy If we want to adjust the marks by multiplying a weight, we can change the cell D2 to =(B2+C2)/2*$E$1 and put a weight to E1.

12. Relative Address • A relative address in a formula is the location of the specified cell or range relative to the cell that contains the formula. • If you copy a formula that contains a relative address, Excel adjusts the address to reflect the new location. • For example, if you type =A2 in A4, it means “2 cells up”. If you copy the contents of A4 to B5, the contents of B5 will be =B3, still “2 cells up”.

13. Absolute Address • An absolute address in a formula (specified by “$” before both column letter and row number in the cell address) is the specified range or cell address. • If you copy a formula that contains an absolute address, the address does not change. • For example, if you type =$A$2 in A4, it means A2. If you then copy the contents of A4 to B5, the contents of B5 will still be =$A$2.

14. Mixed Address • A mixed address in a formula is an address that contains both relative and absolute references. • If you copy a formula that contains a mixed address, Excel adjusts the relative part, but not absolute part. • For example, if you type =$A2 in A4, column A is absolute and row 2 is relative. If you then copy the formula from cell A4 to B5, the contents of B5 will be =$A3.

15. Challenge Problem 1 * Construction of a multiplication table. a) Fill the numbers 1 to 10 into range A2 to A11 b) Fill the numbers 1 to 10 into range B1 to K1 c) Enter a formula in B2 (? Formula) d) Copy the formula in c) to the range B2 to K11 to complete the table.

16. =$A2*B$1

17. Challenge Problem 2 * Construction of a mortgage table. a) Fill the numbers 2.00% to 4.00% (interval=0.25%) into range A2 to A10. b) Fill the numbers 5 to 30 (interval=5) into range B1 to G1 c) A1 stores the loan (say 100,000) d) Enter a formula in B2 (? Formula) e) Copy the formula in c) to the range B2 to G10 to complete the table.

19. 7.4 Spreadsheet as a Tool for Developing Explorative Learning Environment • Macro Macro is a series of commands that Excel carries out automatically.

20. Record a Macro Step1: Select Tools->Macro->Record New Macro

21. Step2: In the Macro Name Box, fill in a name. Step3: [Optional]Short Cut Key, Store Macro In Step4: Click the OK button.

22. Step5: Carry out the actions that you want to record Step6: Click the Stop Macro button. [ To record a Macro with relative address, click the option Relative Reference before Step 5.] Stop Macro button

23. Execute a Macro Step1: Select Tools->Macro->Macro… Step2: In the Macro Name Box, select a name. Step3: Click the Run button. OR Press Short Cut Key Run button

24. Edit a Macro Step1: Select Tools->Macro->Macro… Step2: In the Macro Name Box, select a name. Step3: Click the Edit button. Step4: Make the necessary changes. Edit button

25. Create a Macro By Visual Basic • Macro is actually written in Visual Basic, so you may create macro by yourself. • For more information about Simple Visual Basic, you may refer to my homepage. • For more advanced VB, refer to other references.

26. Challenge Problem 3 • Develop an ILE for students learning the relationship between two quantities (say x and y) by using macro.

27. Challenge Problem 4 • Develop an ILE for solving the following problem. A student has $90. He wants to buy hotdogs and drinks. The price for hotdog is $7@ and that for drink is $5@. The number of drinks should be half of the number of hotdogs. What is the maximum number of hotdogs and drinks that the student can buy?

28. THE END