Target Curriculum: Computer/Technology
Target Grade: 6 - 8
SOLs:
C/T.GR8.1 MAT.7.19
Time:
45-50min
Objective:
Students will create a spreadsheet given a set of data.
Students will insert formulas into the spreadsheet to find the sum, max value, minimum value and mean.
Students will manipulate data in a spreadsheet to see the effects of zeros on assignments.
Purpose:
The purpose of this lesson is to reinforce the skills needed in
creating a spreadsheet. This lesson will also allow the students to
practice their skills of entering data and setting up formulas. In
addition, this lesson will allow the students to see a practical use
for spreadsheets.
Materials:
Computer with OPENoffice CALC
List of data containing a mock set of grades:
Example:
homework
classwork
quizzes
tests
85
90
83
88
72
86
85
90
Procedure:
For the purpose of this lesson, students will be using Microsoft Works spreadsheets.
1. Open OPENoffice CALC on the desktop
2. Select Work Tools
3. Select Spreadsheet
Students should have a
new spreadsheet at this time. Instruct class to enter the data into the
spreadsheet. Columns are labeled A, B, C, etc. and rows are labeled by
numbers. In cell A1 have the student type in the header HOMEWORK . In
cell B1 type CLASSWORK, cell C1 QUIZ, and cell D1 TEST. Continue in
this manner if you have other categories. Next, students will enter the
grades for each category into their spreadsheet. For example, if Joe
scored and 85 on a homework assignment, he would enter an 85 in cell A2.
Example :
A
B
C
D
1
Homework
Classwork
Quiz
Test
2
85
90
83
88
3
72
86
85
90
4
65
77
88
64
5
90
92
76
6
100
84
7
88
Once all the data has been entered, discuss with class this is a technique that a teacher could use to keep his/her gradebook. It is also a great way for the student to keep track of his or her grades throughout the year.
It is now time to use
formulas in the spreadsheet. Explain to the students that instead of
calculating the numbers yourself, the spreadsheet will let you insert
formulas and do the calculations for you. There are several ways to
insert formulas. The first step in using a formula is to select the
cell in which you want the answer to appear. For example, under the
homework column, you can select cell A9 to put the sum of the scores.
To use the sum formula you may type in the formulas =sum(A2:A7) and
then hit
These steps are the
same when it comes to finding the average. This is what you would focus
on in the lesson since grades are averages. The formula for average
(mean) is =avg(A2:A7) this will give you the average in the homework
column.
=avg(B2:B6) will give you the average in the classwork column. Make
sure you first select the cell in which you want you answer to appear.
Another way to find the average is to select the Easy Calc (just like
you did for the sum) and select average. Follow the steps as you did
for the sum.
Other formulas you may
want to include are the max grade or minimum grade. The steps are the
same, just use the different formula
=max(A2:A7)
=min(A2:A7)
Once the students have completed their spreadsheet, have them go back and manipulate the data. Change some of the grades to zeros and watch the averages drop!! This might wake some of them up.
After the student finds the average of each category, they can then find the overall average. Each category average must be in the same row to calculate the overall average. Follow the steps above to find the student's final grade.
Observations:
While students are working on the spreadsheet, make sure they are
inserting the data into the spreadsheet correctly. When using formulas,
make sure students are entering all parts of the formula, especially
the =.
Monitor the students as they go through the steps of creating a
spreadsheet and entering data and formulas. One mistake can throw the
lesson off course.
Once the students begin to manipulate the data, question them on the effects of earning zeros on assignments.
Conclusions:
This lesson is practical because students can actually do this at home
or school. Spreadsheets are used in schools, businesses and homes. Can
you name some of the places that have uses for spreadsheets? What could
they use them for?
Some answers: organizing bills, payroll, grades, budgeting
Extension:
Students can also create graphs and charts to visually represent
the data they have entered. Have students try this with their own
grades.
Class Discussion Questions:
How could a spreadsheet help you to organize something at home?
What other uses are there for spreadsheets?
What happened to your average when you replaced a grade with a zero?
Cautions and Concerns:
Students should have some working knowledge of spreadsheets before
they do this lesson. This is a great reinforcement for the use of
spreadsheets, but now such a great introduction. Spreadsheets need to
be taken one step at a time until the student has the basics.
Comments from Author:
For help on the basics of Microsoft Works, the book
Microsoft Works: Step by Step Windows Version 4.0
by George & Helen Lynch is a great resource.
Computer Literacy Press
Written By:
Jennifer Williams
Submitted By:
Jennifer Williams jennifer@pms.poquoson.k12.va.us
Grade Taught:
6 - 8
School:
Poquoson Middle School
Division:
Poquoson City Public Schools