Yale-New Haven Teachers Institute Home

Statistics and Spreadsheets

by
John P. Crotty


Contents of Curriculum Unit 86.05.04:

To Guide Entry


“We can’t beat Hillhouse; they’re too good!” As coach of the Lee High School Girls Basketball Team, I battled that attitude for five seasons. From 1978 to 1983 Hillhouse beat us eight straight times. Finally on February 22, 1983, Mary Juarbe, an overachiever who wasn’t intimidated by anyone, led us to a 61-58 victory. There were other factors: the emergence of Rasheedah Wali; the pride of Pam Caddell; the friendly confines of the frigid “Lee Ice Palace”; the half court basket by Neyse Sayles at the end of the first quarter; and the encouragement and friendly wager of Robyn Sayles, a former player and Neyse’s older sister.

I have reflected on and learned from the successes and the failures of my girls. There are parallels between the gym and the classroom. As a teacher, my first objective is to develop a winning attitude in my students. Too many students believe that they’re losers, that they can’t learn, that they can’t succeed. In order to change this perception of themselves, students need to win. But, they need to work for the win. Work is an essential point. It must be meaningful. It must be mastered. Students will not believe in themselves just because you tell them that they’re winners or because they succeed in a contrived situation. I didn’t tell my girls to run the offense; I told them to WORK the offense. I couldn’t just tell my girls they were a good basketball team; they had to beat Hillhouse to prove it to themselves.

Mary graduated and went to college on a basketball scholarship. The next year so did Rasheedah. My players realized that future opportunities would be available if they learned the skills I taught. In last year’s curriculum unit, The Measurement of Adolescents, Michael Burgess and Joseph Cummins discuss the concept of schoolwork. They state that there must be a link between the classroom and the real world. In my classroom spreadsheets are the link. They are real work. For the past three summers, I have received a fellowship from Connecticut Business and Industry Association to design spreadsheets on personal computers for corporations. I tell this to my students. I pass this real life skill on to them. My students believe they can master spreadsheets and, if they wish to enter the work force, earn a starting salary of $13,000.

Once your students start to believe, you have to reinforce that belief every day. In the gym we spend a half hour every day working on layups and foul shots. By the end of the season, my girls believe a layup is automatic. If they miss the first foul shot, they believe they will sink the next one. If students will accept repetition in the gym, they’ll accept it in the classroom. We start off each day with drill activities. My students start to think that spreadsheets are automatic.

My teams were never known for executing fancy complicated offenses. I found that if you made the plays too involved, the players wouldn’t learn them. And if they did, they would concentrate more on moving to the right position than on scoring. The trick is to run a simple, fundamentally sound pattern that attempts to maximize each player. My students will tell you that spreadsheets are easier than programming.

Burgess and Cummins also state that the classroom environment strongly affects learning. The gym was cold. The blowers never worked properly. Every year in my budget, I always included a sweat suit for each player. My girls liked receiving a pair of sweats. It made the cold bearable. We would discuss how our adapting to the cold gave us a home court advantage. The Hillhouse coach complained about the lack of heat. The following year I waited for him with all the doors open. We won that game too. I am very fortunate to have seventeen computers in my classroom. They are Radio Shack Model 4 with 64K memory. In most classes every student has his own computer. If I have a larger class, we team up. There’s no problem. I inform any complainers that ten years ago, I had one computer with 4K memory for twenty-five students.

The classroom environment also has an informal component. My students are adolescents subject to immense peer group pressure. I had to try to understand my players, their reasons for playing, and their group dynamics. It took many pizza parties to develop a team spirit. My assistant was fond of telling the girls that he would treat them to pizza if they won a big game. In 1985, we’re playing Hand in the semifinals of the State Tournament and we’re down by twelve points. Nothing is working. An opposing fan in the stands yells, “Pizza is not working, what are you going to do now?” I stand up, “Alright girls, pepperoni on the pizza.” We won the game. Getting students to commit themselves to the effort of learning is the crux of education. I try to get to know my students in situations other than the classroom. I try to learn what motivates them. I try to learn how they are affected by their friends.

All the pizza in New Haven, would not have been enough to make my girls a cohesive group. These were the girls who had beaten Hillhouse, who then worked together for two summers in the ninety degree heat. It took another victory, this time over Cross, before the girls finally became a team and allowed Sylvia Brown to be the group leader. Sylvia led well. I still don’t know what she said during the halftime of the Crosby game. We were up by two points but not playing well. After my brief talk, Sylvia marched the girls into another locker room. We won by thirty. I gave Sylvia the credit in the newspaper.

It may be harder to develop a team work-group environment in the classroom. Normally, we only teach a student for one year. Also, the particular group of students is together only in our one class. However, spreadsheets offer the opportunity for students to work together. Spreadsheets offer success to varied types of students, not just the ones who are academically oriented. Students who can type, students who can see relationships, students who want to be unique, students who work slowly, all can help each other, all can be part of the team.

The effect of the peer group in the classroom environment is similar to the formal and informal organization in business. Peter Drucker has said that conflict arises in a worker when management tells him to do one thing and his group association tells him to do another. It is analogous to life in the Middle Ages where a person owed allegiance to both the Church and the State. And even though the Church was strong, when a conflict arose in a temporal matter, the person chose the State. The Church may have determined life in the next life, but the State determined the present existence.

If a student can realize that the education he receives in school can help advance his standard of living, while his peer group will change after high school, he will choose school.Spreadsheets are a marketable skill. At this point in time there is a shortage in industry for qualified individuals who possess this skill. I am not saying or do I wish to imply that this is all that our students can do. But, for some, it is a start. As their confidence grows so can their job assignment. For others, it is an excellent base for college. The student is learning the logic inherent in the business curriculum. For all, it is the opportunity to discover that they can be successful outside of their particular peer group. In my two years of teaching spreadsheets, I have seen tremendous attitude adjustments. I have seen students look forward to coming to class because they know they will have fun solving the work.

I would like to repeat that spreadsheets are for everyone, not just the gifted. Masuk High School currently uses them as the third year of its general mathematics sequence. After a lively class this year, a student told me, “Karate, you teached good today.” Spreadsheets are better than calculators because the numbers stay on the screen. You can see where the answers came from. When you solve a problem, you are reinforced by the computer.

In the pages that follow, I have built a spreadsheet using one of my player’s accomplishments. That is the key, create problems that involve your students. I have used ages, weights, number of siblings, time spent listening to the radio and other facts relating to the student as data. I received my compliment after a lesson where the class discussed different types of personality. (Yes, I then did teach him about adverbs and irregular past tenses. Yes, my student did pass.) If you use the spreadsheet method, I’m sure your students will pass too.

Please allow me one final plug. Last year’s curriculum unit on the measurement of adolescents presents many good strategies, examples and theories that can help any teacher. I strongly recommend that you get and read the unit.

to top


Lesson 1

Objectives.

1. To create alphabetic labels.
2. To correct mistakes.
3. To save files.

Directions:

1. Move to each of the following cells and enter the label. To enter a label just type its letters and then press [ENTER].
(If you make a mistake, you can erase the last character you typed with [BACKSPACE].)

CELL LABEL
A1 Basket
B1 Buster
A6 Opponent
A8 Norwalk
A9 Hamden
A1C Wilby
A11 Crosby
A12 H’house
A13 Harding
A14 Cross
A15 W Haven

2. The pointer-movement keys may be used to complete an entry. After typing a label, press the down-arrow key to enter and move in 1 keystroke.
CELL LABEL
A16 Masuk
A17 W Haven
A18 Bassick
A19 Val Reg
A20 Hamden
A21 Hand
A22 Cross
A23 H’house
Notice that the default setting for a label is left-aligned.

3. Check your work. If you have a mistake, you may correct it by:
a) Retyping the entry. You do not have to erase the old entry; the new entry takes precedence.
or

b) Entering the edit mode. (Press function key [F2].) This puts the entry in the control panel and allows you to change character-by-character.

4. When you have completed entering the labels, use the pointing method to save the file. Name the file BASKET. (1-2-3 was designed with the pointing method in mind. You are able to examine your options and you can’t make typing errors.)
Steps to save a file:

Press / to bring up the Main Menu.

Press the right-arrow key 4 times to highlight File.

Press [ENTER] to bring up the File Menu.

Press the right-arrow key 1 time to highlight Save.

Press [ENTER]

Type in the filename, BASKET

Press [ENTER]

Instead of pointing, you may also select menu options by typing the first letter of the option.

Alternate steps to save a file:

Press / to bring up the Main Menu.

Type F for File.

Type S for Save.

Type in the filename, BASKET

Press [ENTER]

to top


Lesson 2

Objectives:

1. To retrieve a file.
2. To create labels as right-aligned, centered or repeating.
3. To use non-alphabetic characters as labels.
4. To move the cursor with the GOTO key [F5].

Directions:

1. Let’s bring back the work you’ve already done.
Steps to retrieve a file:.

Press / to bring up the Main Menu.

Press the right-arrow key 4 times to highlight File.

Press [ENTER] to bring up the File Menu.

Retrieve is the highlighted choice; press [ENTER]

Press the right-arrow or the left-arrow until BASKET is highlighted. Since BASKET is the last file you created, moving left will probably be faster.

2. Move to the following cell and create a centered label. Start the entry with a ”, the label-prefix for centered.
CELL LABEL
D3 Pam
3. At each of the following cells create right-aligned labels. Start each entry with a “, the label-prefix for right-aligned.
CELL LABEL
C5 Field
C6 Goals
D5 Foul
D6 Shots
E5 Total
E6 Points
4. If a non-alphabetic character is used as the first character in a label, you must begin the entry with a label-prefix. Otherwise, 1-2-3 will beep you. It will think that you have made a mistake in entering a numeric value. Move to each of the following cells and make the entry right-aligned.
CELL LABEL
——————
C7 ———
D7 ———
E7 ———
5. At each of the following cells create repeating labels. Start each entry with a \,the label-prefix for repeating.
CELL LABEL
C4 -
D4 -
E4 -
A2 =
B2 =
6. Move to the following cell and create a left-aligned label. Start the entry with a ‘, the label-prefix for left-aligned.
CELL LABEL
——————
A7 ———
7. When you have completed entering the labels, resave the file. Use the same filename BASKET.
Steps to resave a file:

Press / to bring up the Main Menu.

Press the right-arrow key 4 times to highlight File.

Press [ENTER] to bring up the File Menu.

Press the right-arrow key 1 time to highlight Save.

Press [ENTER]

1-2-3 now prompts you with BASKET, the file you loaded.

Press [ENTER] to accept 1-2-3’s choice.

Press the right-arrow key 1 time to highlight Replace.

Press [ENTER]

8. Now that you are becoming familiar with 1-2-3’s menu structure, I will explain the above keystrokes as:
Press / to bring up the Main Menu.

Select File.

Select Save.

1-2-3 now prompts you with BASKET, the file you loaded.

Press [ENTER] to accept 1-2-3’s choice.

Select Replace.

to top


Lesson 3

Objective:

1. To enter values.

Directions:

1. Move to the following cells and enter the values indicated. To enter a number just type its digits. Finish the entry by pressing [ENTER] or by pressing a pointer-movement key.
Use the numbers on the top row of the keyboard. Do not use the keypad to enter numbers. These keys are used to move the cursor.
CELL VALUE
C8 5
C9 12
C10 7
C11 18
C12 2
C13 15
C14 11
C15 10
C16 12
C17 14
C18 15
C19 14
C20 8
C21 8
C22 5
C23 8
D8 6
D9 1
D10 4
D11 1
D12 9
D13 2
D14 6
D15 4
D16 3
D17 7
D18 1
D19 1
D20 5
D21 5
D22 6
D23 7
Notice that numbers are right-aligned.

2. When you have completed entering the numbers, resave the file.
Press / to bring up the Main Menu.

Select File.

Select Save.

1-2-3 now prompts you with BASKET, the file you loaded.

Press [ENTER] to accept 1-2-3’s choice.

Select Replace.

to top


Lesson 4

Objectives:

1. To create formulas using the pointing method.
2. To create formulas using the keyboarding method.

Directions:

1. Using the pointing method, find Pam’s points for the first 8 games. 1-2-3 likes this method because it minimizes typing mistakes and because it allows you to see the actual cells that you are using.
Move the cursor to E8.

Press + to start the formula.

Press the left-arrow key twice to obtain for our formula the cell which contains the number of baskets scored. The control panel now displays +C8.

Press * to multiply.

Type 2 to multiply by 2 since each basket is worth 2 points.

Press + to add.

Press the left-arrow key once to obtain for our formula the cell which contains the number of foul shots made. The control panel now displays +C8*2+D8.

Press [ENTER] to enter the formula.

Move the cursor to E9 and use the above procedure to find Pam’s points for the Hamden game.

Continue through the West Haven game.

2. Using the keyboarding method, find Pam’s points for the next 8 games. You will need to know this method when you are in the Edit Mode.
Move the cursor to E16.

Press + to get into Value Mode.

Type C13*2+D13 to create the formula.

Press [ENTER] or the down-arrow key to enter the formula.

Move the cursor to E17 and use the above procedure to find Pam’s points for the Cross game.

Continue through the remaining games.

3. When you have completed entering the formulas, resave the file.
Press / to bring up the Main Menu.

Select File.

Select Save.

1-2-3 now prompts you with BASKET, the file you loaded

Press [ENTER]

Select Replace.

to top


Lesson 5

Objectives:

1. To use 1-2-3’s Statistical Functions.

Directions:

1. Move to the following cells and create left-aligned labels.
CELL LABEL
A24 ———
A25 Total
A27 Maximum
A28 Minimum
A30 # of Games
2. Move to the following cells and create right-aligned labels.
CELL LABEL
C24 ———
D24 ———
E24 ———
3. Using the SUM function, find the total of Pam’s points.
Move the cursor to E25.

Type SUM(

Did you see the (bracket? Quite often I miss it.

Press the up-arrow key 17 times to move the cursor to E8, the first value in the range. Notice how the control panel now says sum(E8

Press . to anchor the range at E8.

Press [END]

Press the down-arrow key. (The range will be highlighted in blue. You do want to sum to the line.)

Type ) to finish the formula.

Press [ENTER] to enter the formula.

The SUM function is probably the most used function. When possible, include an extra row in your SUM range. The extra cell E24 contains a label, a set of hyphens. 1-2-3 gives labels a value of 0. Therefore, including this cell in your range will not affect the value of the SUM function. But, including the cell provides a way to add a new row at the bottom of the column without forcing you to edit the SUM function.

4. Using the MAX function, find Pam’s high game.
Move the cursor to E27.

Type MAX(

Press the up-arrow key 19 times to move the cursor to E8, the first value in the range.

Press . to anchor the range at E8.

Press the down-arrow key 16 times to expand the range through E24, the line below the points.

Type ) to finish the formula.

Press [ENTER] to enter the formula.

You may include the extra cell in the MAX range. Hopefully your maximum will be greater than zero.

5. Using the MIN function, find Pam’s low game.
Move the cursor to E28.

Type MIN(

Press the up-arrow key 20 times to move the cursor to E8, the first value in the range.

Press . to anchor the range at E8.

Press the down-arrow key 15 times to expand the range through E23, the last value in the range.

Type ) to finish the formula.

Press [ENTER] to enter the formula.

You may not include an extra cell in the MIN range. 1-2-3 will treat the label as a zero point game.

6. Using the COUNT function, count the number of games Pam played.
Move the cursor to E30.

Type COUNT(

Move the cursor to E8, the first value in the range.

Press . to anchor the range at E8.

Move the cursor to E23, the last value in the range.

Type ) to finish the formula.

Press [ENTER] to enter the formula.

You may not include an extra cell in the COUNT range. 1-2-3 will treat the label as a zero point game.

7. When you have completed entering the formulas, resave the file.

to top


Lesson 6

Objectives:

1. To draw a “no-frills” graph.
2. To name a graph.
3. To use function key [F10] to view a graph.

Directions:

1. It is easy to draw a minimal graph.
Press / to bring up the Main Menu.

Select Graph.

Select A.

Move the cursor to E8, the first value in the range.

Press . to anchor the range.

Press the down-arrow key 15 times to highlight the range.

Press [ENTER]

That’s all!

Select View to view the graph.

Note that a line graph is the default type of graph. Also, that the Y-axis is automatically set to fill as much of the screen as possible.

Press any key to continue.

2. Let’s name our first graph. I’ll call mine PAM.

Select Name.

Select Create.

Type in the name, PAM

Press [ENTER]

3. Besides line graphs, 1-2-3 can also draw other types of graphs.

Select Type.

Select Bar.

Select View.

Press any key to continue.

Select Type.

Select Pie.Select View.

Press any key to continue.

And let’s quit the graph routine.

Select Quit.

4. Any time you are in the Ready mode, pressing [F10] draws the last graph on the screen. This allows you to make changes and see their impact instantly.
Move the cursor to C12.

Type 8 which is how many baskets Pam scored in the rematch.

Press [ENTER]

Press [F10] to view the changes.

Notice how the Hillhouse game improves from 3.1% to 5.8%

Press any key to continue.

Type 2 to put back the correct number of baskets.

Press [ENTER]

And unfortunately we’re back to 3.1%

5. Make sure you save your worksheet. Your graph is coupled with this worksheet’s file. I can not tell you how many times I have lost my graphs by not saving my worksheet.

to top


Lesson 7

Objectives:

1. To name a range.

2. To draw an improved graph.

Directions:

1. Let’s list the opponents along the X-axis. Since there is limited room along the axis for names, we’ll use abbreviations. Move to the following cells and enter the labels.
CELL LABEL
B8 N
B9 Hm
B10 W
B11 Cb
B12 HH
B13 Hr
B14 C
B15 WH
B16 M
B17 WH
B18 B
B19 VR
B20 Hm
B21 Hd
B22 C
B23 HH
2. A powerful tool in 1-2-3 is the ability to assign a name to a range of cells. Naming ranges makes your formulas more meaningful and more easily read. The formulas will automatically adjust when the range is moved or changed. And maybe most importantly, you have the ability to access these named ranges from other worksheets.
Let’s name the range of opponents, OPPONENTS.

Move the cursor to B8.

Press / to bring up the Main Menu

Select Range.

Select Name.

Create is highlighted; press [ENTER]

Type in the name, OPPONENTS

Press [ENTER]

The control panel now prompts for the location. Since we’re already on the first cell and since the 2 periods tell us we’re already anchored,

Press [END] to indicate we want to jump.

Press the down-arrow key to show the jump’s direction. (All 16 abbreviations should now be highlighted.)

Press [ENTER]

3. Now let’s improve our graph’s appearance.
Press / to bring up the Main Menu.

Select Graph.

Type is highlighted; press [ENTER]

Line is highlighted; press [ENTER]

Select X.

Type OPPONENTS for the range.

Press [ENTER]

Select Options.

Legend is highlighted; press [ENTER]

A is highlighted so press [ENTER]

Type Pam

Press [ENTER]

Select Titles.

First is highlighted so press [ENTER]

Type Basket Busters

Press [ENTER]

Titles is highlighted; press [ENTER].

Select Second.

Type 84 85

Press [ENTER]

Titles is highlighted; press [ENTER].

Select Y-Axis.

Type Points Scored

Press [ENTER]

Select Color

It will seem like nothing has happened, but you have just turned on Color.

4. Let’s name this graph. I’ll call mine Pam1. Select Quit to leave the Options Menu.
Select Name.

Select Create.

Type Pam1

Press [ENTER]

5. To view the graphs

Name is highlighted so press [ENTER].

Use is highlighted so press [ENTER].

Highlight whichever graph you wish to see.

Press [ENTER]

Press any key to continue.

6. To leave the graph routine and return to the Ready mode.
Select Quit.

7. Again, be sure to save your work.

to top


Lesson 8

Objectives:

1. To copy a cell.

2. To examine relative cell addresses.

3. To review naming a range.

Directions:

1. Let’s find the totals, the maximum and the minimum for Pam’s field goals and foul shots.
Move the cursor to E25.

Press / to bring up the Main Menu.

Select Copy.

Press [ENTER] since we just want to copy the 1 cell.

Press the left-arrow key 1 time to move to the first target.

Press . to anchor the target.

Press the left-arrow key 1 time to move to the last target.

Press [ENTER]

Repeat the above procedure at cells E27 and E28.

2. Let’s compare the formulas in column D and column C to their original formula in column E. Notice that the formulas are not duplicate copies of the formulas in column E. Instead, they adjusted to the columns in which they appear.
The formulas are different because unless you specify otherwise, cell addresses used in formulas are relative. 1-2-3 reads the formula in cell E25 as add the cells that lie in range from 17 rows above me to 1 row above me.

1-2-3 copies this relationship to the other 2 columns. Column D and column C use the same relationship. Add the cells that lie in the range from 17 rows above me to 1 row above me.

3. Let’s create another range, PAMDATALABEL.
Press / to bring up the Main Menu.

Select Range.

Select Name.

Create is highlighted; press [ENTER]

Type in the name, PAMDATALABEL

Press [ENTER]

Press [BACKSPACE] to remove the anchor.

Move the cursor to G8, the first location.

Press . to anchor G8.

Move the cursor to G23, the last location.

Press [ENTER]

Let’s enter a label in this range.

Move the cursor to G13.

Enter the label POW!

4. Save your work.

to top


Lesson 9

Objectives:

1. To use a named graph.

2. To draw an enhanced graph.

Directions:

1. One thing still bothers me about our line graph. Pam had to work hard for her points. I do not feel that the line graph’s lower limit of 10 properly reflects Pam’s achievements. Let’s manually set the lower limit to 0.
Press / to bring up the Main Menu.

Select Graph.

Select Name.

Use is highlighted; press [ENTER].

Select Pam1.

Press any key to continue.

We now have Pam1 with all its settings in memory.

Select Options.

Select Scale.

Y Scale is highlighted so press [ENTER]

Select Manual.

This is another one of those choices where nothing seems to happen, but you will now have to specify an upper and lower scale limit.

Select Lower.

Press [ENTER] to accept the prompt value of zero.

Select Upper

Type 40 to show how to override a prompt value.

Press [ENTER]

Select Quit to leave the Scale Menu.

2. To view the graph

Select Quit to return to the first level of the Graph Menu.

Select View.

Press any key to continue.

3. Let’s get creative and add a data-label. The Hillhouse game was very physical. We were more concerned with beating on them then beating them. We’ll use a data-label to comment.
Select Options.

Select Data-Labels.

Select A.

Type PAMDATALABEL to use the range we created last lesson.

Press [ENTER]

Select Below.

Select Quit to leave the Data-Labels’ Menu.

4. To view the graph

Select Quit to return to the first level of the Graph Menu.

Select View.

Press any key to continue.

5. The new graph looks good; let’s save it.

Select Name.

Select Create.

Type Pam2

Press [ENTER]

6. Make sure you save the worksheet, or your new graph will not be saved.

to top


Lesson 10

Objectives:

1. To find the arithmetic mean.

2. To format a number.

3. To examine absolute cell addresses.

Directions:

1. Move to the following cell and create a left-aligned label.

CELL LABEL
A26 Average

2. The mean is probably the most familiar measure of central tendency. In everyday life, the mean is referred to as the average. To find the mean of Pam’s field goals, divide the sum of the field goals by the number of games.
Move the cursor to C26.

Press + to get into Value Mode.

Move the cursor to C25 to get the sum of the baskets.

Press / to divide.

Move the cursor to E30 for the number of games.

Press [ENTER] to enter the formula.

3. Let’s make the number’s appearance more understandable.

Press / to bring up the Main Menu.

Select Range.

Select Format.

Select Fixed.

1-2-3 offers 2 as the suggested number of decimal places.

Type 1 to override 1-2-3’s prompt.

Press [ENTER]

4. Repeat steps 2 and 3 in cells D26 and E26 to find the mean of Pam’s foul shots and points.
If you use the Copy command to copy the formula from C26 to D26 and E26, you will not get the right answer the way the formula is currently set up. Remember, 1-2-3 copies an address as a relative address. In this case, take the cell above me and divide it by the cell located 4 below and 2 to the right.

The formula that we used at C26 won’t work at D26. We’ll slide right past E30. We always want to divide by E30. When you always want the same cell, you want an absolute address. To make a cell absolute,type a $ before the letter and another $ the number of the cell.

If you wish to use the Copy command, write the original formula as +C25/$E$30

Notice that Copy also copies the cell’s format.

5. When you have completed entering the formulas, resave the file.

to top


Lesson 11

Objectives:

1. To find the deviation from the mean.

2. To find the sum of the deviations from the mean.

3. To create a formula using absolute and relative cell addresses.

Directions:

1. Move to the following cells and create right-aligned labels.

CELL LABEL
F4 Deviation
F5 from the
F6 Mean
F7 ———
F24 ———

2. Let’s see how many games Pam scored above her average and how many games Pam scored below her average.
Move the cursor to F8.

Press + to get into Value Mode.

Move the cursor to E8 to get Pam’s points for Norwalk.

Press to subtract.

Move the cursor to E26 to get Pam’s average.

Press function key [F4] to make E26 absolute.

Press [ENTER] to enter the formula.

3. Let’s make the number’s appearance more understandable.

Press / to bring up the Main Menu.

Select Range.

Select Format.

Select Fixed.

1-2-3 offers 2 as the suggested number of decimal places.

Type 1 to override 1-2-3’s prompt.

Press [ENTER]

4. Let’s copy the formula for the rest of the games.

Press / to bring up the Main Menu.

Press [ENTER] since we just want to copy the one cell.

Type F9 as the first cell in the target range.

Press . to anchor the target.

Type F23 as the final target.

Press [ENTER]

5. We’ve already created an SUM formula. Let’s copy it.

Move the cursor to E25.

Press / to bring up the Main Menu.

Press [ENTER] since we just want to copy the one cell.

Type F25 as the target range.

Press [ENTER]

Notice that the sum of the deviations from the mean is zero!

6. Resave the file.

to top


Lesson 12

Objectives:

1. To find the variance.

2. To find the standard deviation.

3. To use the SORT function.

Directions:

1. Move to the following cells and create right-aligned labels.

CELL LABEL
G5 Deviation
G6 Squared
G7 ———
G24 ———
2. Move to the following cells and create left-aligned labels.

CELL LABEL
——————
A32 Variance
A33 Standard Deviation

3. Since the sum of the deviations from the mean is zero, we need another measure to help us analyze Pam’s points. Let’s square each deviation. Squaring a number produces all positive products. We can add these numbers and get a non-zero value.
Move the cursor to G8.

Press + to get into Value Mode.

Move the cursor to F8 to get the deviation for Norwalk.

Press * to multiply.

Move the cursor to F8 to get the deviation for Norwalk again.

Press [ENTER] to enter the formula.

4. Let’s make the number’s appearance more understandable.

Press / to bring up the Main Menu.

Select Range.

Select Format.

Select Fixed.

1-2-3 offers 2 as the suggested number of decimal places.

Type 1 to override 1-2-3’s prompt.

Press [ENTER]

5. Let’s copy the formula for the rest of the games.

Press / to bring up the Main Menu.

Select Copy

Press [ENTER] since we just want to copy the one cell.

Type G9 as the first cell in the target range.

Press . to anchor the target.

Type G23 as the final target

Press [ENTER]

6. We’ve already created an SUM formula. Let’s copy it.

Move the cursor to F25.

Press / to bring up the Main Menu.

Select Copy

Press [ENTER] since we just want to copy the one cell.

Type G25 as the target range.

Press [ENTER]

7. Variance is the sum of the squared deviations from the mean divided by N.

Move the cursor to G32.

Type G25 to get the sum.

Press / to divide.

Type E30 to divide by the number

Press [ENTER]

8. The standard deviation is the square root of the variance. I found this function was extremely important in designing the defense. It showed me where our points could come from so I could determine how many points we could allow and still win.
Move the cursor to G33.

Press to prepare for a library function.

Type SORT( for the square root function.

Type G32 for the argument of the function.

Press ) to finish the formula.

Press [ENTER]

9. Resave the file.

to top


Bibliography

Blanc, Iris and Venta, Cathy. Spreadsheets, Skill Building Exercises and Applications. New York, N.Y. Dictation Disc Co. 1986.

Curriculum Units by Fellows of the Yale-New Haven Teachers Institute. The Measurement of Adolescents. New Haven, CT. Yale-New Haven Teachers Institute. 1984.

Drucker, Peter F. The New Society. New York. Harper Brothers Publishers. 1949.

Runyon, Richard P. and Audrey Haber. Fundamentals of Behavioral Statistics. Reading, MA. Addison-Wesley Publishing Company. 1984.

to top

Contents of 1986 Volume V | Directory of Volumes | Index | Yale-New Haven Teachers Institute

© 2014 by the Yale-New Haven Teachers Institute
Terms of Use Contact YNHTI