Spreadsheet
Application Workshop
Using Microsoft Excel
Spreadsheet applications have many
classroom uses. For you, the busy
teacher, electronic spreadsheets keep record-keeping to a minimum of time and
energy. For you, the ever-so-diligent
catalyst for knowledge acquisition, spreadsheets can help you help children see
connections and visualize abstract concepts.
The purpose of this workshop is to have you experience some of the “fun”
on spreadsheet applications. Again, I
have put the methods I used to achieve the attached handout for your information. Please follow the basic procedures I used to
complete the “Class Roster/Grade Sheet” and the graphic analysis.
1.
All
spreadsheets are divided into columns and rows:
thus, a cell identified as B15
represents the column designated by “B” and the 15th row on that
particular spreadsheet. The contents of
each cell represent variables – either numeric or alphanumeric. Those variables can either stand alone or be
linked through formulas to determine the value in another cell (see 3 below).
2.
The
first thing you must do to create this spreadsheet is to give labels to the
columns. Just type the column headings
in each column and either press the “enter” key to move under the cell or the
“tab or cursor” key to move to the right.
3.
Fill
in the cells with information of your choice – I was in my “J” mood today and I
just wanted to give you an example of some grades. Cell H2
will hold a formula to give a weighted average to the grades. In this case – the three quizzes are 25% of
the grade; Test 1 is 20%; Test 2 is 15%; and the Final test is 40%. The formula for this is: =(Average(B2:D2)*.25)+(E2*.2)+(F2*.15)+(G2*.4) -- This formula will now be copied down the
column by selecting cell H2 and then
going to the “file” menu – select copy – hold down the left mouse key and
highlight rows H3-8 – go to File menu and select paste. Notice that all of the weighted averages are
now appearing in Column H. However, some
of the averages may look strange because of the number of decimal places. Go to Format – select “cells” – reduce the
decimal places to “0”. (NOTE: the easiest way to do this is to include it
in the original formula: =Roundup((Average(B2:D2)*.25)+(E2*.2))+(F2*.15)+(G2*.4),0)
4.
For
#4 you will “paste function” – the funny looking “fx”
– go to the cell B8 and press the Fx icon – select Average and press
return. Copy this to the rest of the
cells and format.
5.
Select
the Class Average row and experiment with the chart mechanisms – (the chart
icon on top of page). View these figures
in different chart styles.
|
NAME |
QUIZ
1 |
QUIZ
2 |
QUIZ
3 |
TEST
1 |
TEST
2 |
FINAL
TEST |
FINAL
GRADE |
|
John |
95 |
93 |
90 |
80 |
85 |
91 |
88 |
|
Jill |
78 |
89 |
90 |
96 |
75 |
89 |
87 |
|
Jack |
69 |
79 |
72 |
84 |
86 |
74 |
78 |
|
Jim |
89 |
90 |
98 |
87 |
76 |
65 |
78 |
|
Jenny |
78 |
89 |
56 |
90 |
99 |
83 |
85 |
|
Jane |
90 |
99 |
98 |
97 |
96 |
65 |
84 |
|
Class
Avg |
83 |
90 |
84 |
89 |
86 |
78 |
83 |
|
Min |
69 |
|
|
|
|
|
|
|
Max |
99 |
|
|
|
|
|
|