The
Spreadsheet
Absolutely
Elementary!
By Elizabeth Dudley Holmes
Elizabeth explains how teacher trainers
and elementary-level
classroom teachers can use spreadsheets to teach
mathematical concepts.
The following is a set of step-by-step instructions for
creating
the spreadsheet used as the main example in “The
Spreadsheet-Absolutely
Elementary!” This spreadsheet helps students
calculate the
total number of travelers in the Mother Goose rhyme
“As I Was
Going to St. Ives.”
The other supplemental Web page for Holmes’ article is for a
spreadsheet template using ClarisWorks Draw. The spreadsheet in this
case is a survey template that tallies the types and numbers of pets owned by
the families of students in a classroom. After you have created this template
and used it with your class, your students can extend the amount of data they
collect by posting the template on the school’s network or intranet, or
by interviewing other students in the school and entering the data themselves.
How
Many Are Going to St. Ives?
As I was going to St. Ives,
I met a man
with seven wives;
Every wife had seven sacks,
Every sack
had seven cats,
Every cat had seven kits;
Kits, cats, sacks, and wives, How many were going
to St. Ives?
This poem was written by Mother Goose. Everyone knows her
name but
no one actually knows who she was. Her true identity is a
mystery.
Mother Goose leaves us another mystery in this rhyme. We
can solve
the mystery of St. Ives using a spreadsheet. Here are the
steps:
- At the ClarisWorks New Document dialog box,
click once
to highlight Spreadsheet. Click OK.
- Choose Format, Insert Header from the top
menu bar.
- A cursor appears.
- Type How Many Are Going to St. Ives?
- Highlight the title.
- Choose Font from the top menu bar. Change
the font
size to 18.
- Choose Style from the top menu bar.
Change the
style to Bold.
- Choose Style from the top menu bar.
Change the
Text Color to the color of your choice.
- Save the spreadsheet. Choose File, Save from
the top
menu bar.
- Type “St. Ives” to title the
spreadsheet.
- Click Desktop to save the document to the
desktop.
- Click Save.
- Point and click cell A1 to select the cell. Note
that “A1”
appears on the left side of the entry bar. Type
“Characters.”
Press the Tab key.
- Tab moves the cursor to cell B1. Type in
“How
Many?.” Press the Tab key.
- In cell C1 type “Character Had: .” Press
Tab.
- In cell D1 type “How Many?.” Press
Tab.
- In cell E1 type “Subtotals.” Press
Return.
- Point to cell A2 and click to select the cell. List
the characters
in the poem in this way:
- Type “I” for the character who speaks in
the poem.
- Press the Return key. The return key moves the
cursor
down one cell.
- In cell A3 type “man.” Press the
Return key.
- In cell A4 type “wives.” Press the
Return
key.
- In cell A5 type “sacks.” Press the
Return
key.
- In cell A6 type “cats.” Press the
Return
key.
- In cell A7 type “kits.” Press the
Return
key.
- In cell A8 type “Total.” Press the
Return
key to enter the entry.
- Point and click cell B2 to select the first cell in
the “How
Many” column.
- Respond to “How Many”
“Iís” were
in the poem. Type 1. Press Return.
- In cell B3 respond to “How Many”
“men”
were in the poem. Type 1. Press Tab to
move to
cell C3.
- Cell C3 is in the “Character Had:” column.
The man
had wives. Type “wives” in cell C3.
Press Tab
to move to cell D3.
- Cell D3 is in the “How Many” column. The
man had
7 wives. Type “7” in cell D3. Press
Tab
to move to cell E3.
- Cell E3 is in the “Subtotals” column. A
formula
will be entered to calculate a subtotal.
- Type an = (equal sign) to start the formula. It
appears
in the entry bar.
- Click cell B3. The data in cell B3 is added to
the formula.
The formula in the entry bar now reads
- Type the multiplication symbol (*). The formula
now reads
- Click cell D3. The data in cell D3 is added to
the formula.
The formula now reads
Press the Return key.
- The subtotal appears in cell E3.
- Copy the formula which will be used to calculate all
of the
subtotals by following these steps:
- Click and hold in cell E3.
- Drag to cell E7 and release the mouse button.
- All cells between E3 and E7 are highlighted.
- Choose Calculate, Fill Down from the top
menu bar.
This action copies the formula to selected cells.
Zeros
appear in rows with no data for computation.
- Click to select cell B4.
- How many wives are in the poem? Type
“7.” Press
Tab to move to C4.
- What did the wives have? Type “sacks.”
Press
Tab to move to D4.
- How many sacks did each wife have? Type
“7.”
Press Return.
- The number of sacks (49) appears in cell E4, the
Subtotal
column.
- Click to select cell B5.
- How many sacks are in the poem? Type
“49.” Press
Tab to move to C5.
- What did the sacks have? Type “cats.”
Press
Tab to move to D5.
- How many cats did each sack have? Type
“7.”
Press Return.
- The number of cats (343) appears in cell E5, the
Subtotal
column.
- Click to select cell B6.
- How many cats are in the poem? Type
“343.” Press
Tab to move to C6.
- What did the sacks have? Type “kits.”
Press
Tab to move to D6.
- How many kits did each cat have? Type
“7.” Press
Return.
- The number of kits (2401) appears in cell E6,
the Subtotal
column.
- Click to select cell B7.
- How many kits are in the poem? Type
“2401.”
- The kits did not have anything, as the poem
reads, so
the subtotals are complete.
- Click cell B8 to enter a formula which will total
those in
route to St. Ives.
- Type an = (equal sign) to start the formula. It
appears
in the entry bar.
- Click cell B2 . The data in cell B2 is added to
the formula.
The formula in the entry bar now reads: = B2
- Click cell B3 . The data in cell B3 is added to
the formula.
The formula in the entry bar now reads: = B2+B3
- Click cell B4 . The data in cell B4 is added to
the formula.
The formula in the entry bar now reads: = B2+B3+B4
- Click cell B5 . The data in cell B5 is added to
the formula.
The formula in the entry bar now reads: =
B2+B3+B4+B5
- Click cell B6. The formula in the entry bar now
reads:
= B2+B3+B4+B5+B6
- Click cell B7. The final formula is: =
B2+B3+B4+B5+B6+B7
- Press Return.
- How many went to St. Ives? The total appears in
cell B8.
2802
- To check for accuracy, choose Calculator from
the Apple
Menu Items. The calculator will appear on the
spreadsheet.
Use the calculator to recompute each step in the
problem.
- To close the calculator, click the close box in
the upper
left corner of the calculator.
- The spreadsheet can be “prettied up” in a
few simple
steps. First select the entire spreadsheet by clicking
the box
at top of the Row Headings and to the left of Column
Headings.
The spreadsheet will be highlighted, or selected.
- Go to Format, Font on the top menu bar.
Select
a favorite font.
- Go to Format, Style on the top menu bar.
Select
a favorite style.
- Go to Format, Text Color on the top menu
bar. Select
a favorite color.
- Go to Format, Alignment on the top menu
bar. Select
Center.
- Go to Format, Size on the top menu bar.
Select
a 12 point size.
- These changes may have caused the cells to become
too small
for your text. Adjust cell height and width in this
way:
- Select the entire spreadsheet (Step 31).
- Go to Format, Column width on the top
menu bar.
Type in the desired point size.
- Go to Format, Row height on the top menu
bar. Type
in the desired point size.
- Minor changes to column width can be made by
manually
adjusting the grid lines between column names.
Add
A Chart to the Spreadsheet
- Let’s create a pie graph to show all of those
who traveled
to St. Ives.
- Click and hold in cell A2. Drag from upper left
to lower
right to highlight through cell B7.
- Go to Options, Make Chart on the top menu
bar.
- At the Chart Options dialog box, click the pie
graph icon.
- Click OK. A chart of the selected data
appears.
Note: The data for “I” and
“man” is
too small to viewed in this display.
- To make modifications to the chart, or to change
the display,
double-click the chart. The Chart Options dialog
box reappears.
Add
Clip Art to the Spreadsheet
- Click the Arrow, or Selector Tool on the
toolbar. This
action will remove the cursor from the workspace.
- Choose File, Library from the top menu bar.
Scroll
through the clip art categories. Select
Animals.
- The Animals window appears.
- Highlight Cat. Click Use.
- The clip art appears in the spreadsheet with edit
handles.
- Size the clip art by clicking handles and
stretching the
image.
- Move the clip art by dragging the image from the
middle.
- Delete the clip art by selecting it with the
arrow tool.
Press the delete key.
Print
the Spreadsheet
- Choose File, Print from the top menu bar.
- The Print dialog box appears.
- To eliminate column headings, row headings, or grid
lines
from the spreadsheet in the printed copy, click to
place an
X in the boxes for each option in the lower
left corner
of the Print dialog box.
- Click Print from the main menu.
Copyright © 1997, ISTE (International
Society for Technology in Education).
All rights reserved.
|
