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
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 -gt 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 that if 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
A B
1 loan 2,000,000.00
2 Interest rate (p.a.) 3.00
3 number of years 20
4 monthly payment 11,091.95
variables

• -PMT(B2/12,B312,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
(B2C2)/2
A B C D E
1 Name Test1 Test2 Term Mark
2 MARY 21 45
3 PETER 45 76
4 JOHN 56 34
5 HELEN 43 65
6 KITTY 45 56
Copy
If we want to adjust the marks by multiplying a
weight, we can change the cell D2 to
(B2C2)/2E1 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 A2 in A4, it means
  A2. If you then copy the contents of A4 to B5,
  the contents of B5 will still be A2.

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
A B C D E F G H I J K
1 1 2 3 4 5 6 7 8 9 10
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
11 10
A2B1
17
Challenge Problem 2

• Construction of a mortgage table.
• a) Fill the numbers 2.00 to 4.00
  (interval0.25) into range A2 to A10.
• b) Fill the numbers 5 to 30 (interval5) 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.

18
A B C D E F G
1 100,000 5 10 15 20 25 30
2 2.00
3 2.25
4 2.50
5 2.75
6 3.00
7 3.25
8 3.50
9 3.75
10 4.00
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-gtMacro-gtRecord New Macro

21

• Step2 In the Macro Name Box, fill in a name.
• Step3 OptionalShort 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-gtMacro-gtMacro
• 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-gtMacro-gtMacro
• 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_at_ and that for
  drink is 5_at_. 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