John P. Crotty
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.
