Communicating Through Spreadsheets

Communicating through Spreadsheets

Target Curriculum: Computer/Technology

Target Grade:
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 Microsoft Excel or Microsoft Works--Spreadsheet
List of data containing a mock set of grades:

Procedure:
For the purpose of this lesson, students will be using Microsoft Works spreadsheets.
1. Open Microsoft Works 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 :

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 . This will total the scores in the homework column. You can repeat this step for each category. Make sure you use the appropriate column letters. For example is you are working with the scores in the B column then use B in your formula. Another way to find the sum is to use the autosum button. Click in the cell you want you total to appear and then click the autosum which is on the toolbar at the top. It looks like a backwards E. ( A sigma). It will give you a formula. If the formula showing is not what you want, then you can highlight the scores you want totaled and then hit . This can also be repeated for each column. The final way to find the total is to go under the Tools menu and select the Easy Calc ( or the little calculator on the tool bar) This will give you options of sum, difference, average, etc. Select the sum and it will then ask for the range you want. Follow the instructions and it will give you the total.

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.