# StDevdemoxls - West Virginia University

Document Sample

```					PS400 Spreadsheet Tutorial

Welcome to a "layered spreadsheet tutorial".

The objective here is to teach you how to use a spreadsheet to
calculate the standard deviation.

Proceed through the tutorial by using the "A" through "J" tabs
down at the bottom of the page

Use the Scroll bar on the far right to move up or down in the page,
and the bottom scroll bar to read off the right (or left) edges of the screen.

The Bold print is new text added to the previous layer of the spreadsheet.

The "plain text" is from the previous page.

The Red Text indicates a cell to be looked at or 'acted upon'.

Italics indicates instructions or explanatory text omitted on the next layer.

Put the square 'cell' cursor on each of the following by clicking on the spot:

The cell address simply uses the letters for the columns
and the numbers for the rows.

Try figuring out the addresses for the cells below.

And Here              And finally here.

5            Now click on these numbers.

5

3             (Also note that all cell formulae must begin with either a +, - , = or parenth
-
5 =          =C40+C41 provides the sum of 2 + 3

Ok, let's calculate a standard deviation.

Click on the Set Up Table tab below.
or the columns

egin with either a +, - , = or parentheses.)
Calculating a standard deviation

Xi             Xi - Mean (Xi - Mean)2                    Enter the labels. Use the subscript and superscript fo
------------------------------------------------------   if available of your version of Quattro Pro

Go to "C"
the subscript and superscript fonts .
rsion of Quattro Pro
Calculating a standard deviation

Xi             Xi - Mean (Xi - Mean)2
-              -
---------------------------------------------------------
1                                               Enter the data
2
3
4
5
6
7
8
9
Go to "D"
Calculating a standard deviation

Xi             Xi - Mean (Xi - Mean)2
-----------------------------------------------------
1
2
3
4
5
6
7
8
9

45                                 Block the text from B7 to B17, and the click on the summation button above.
This puts the sum of X1 to X9 in cell B17
5 = The Mean of X                Divide B17 by 9 to get the Mean

Go To "E"
on the summation button above.
Calculating a standard deviation

Xi             Xi - Mean (Xi - Mean)2
-----------------------------------------------------------
1             -4                                Calculate the deviation by subtracting the mean from
2              2                                 the value of X
3              3                                 +B7-B19
4              4                                Copy this formula to cells C8 to C15 by clicking on the
5              5                                 Copy button, blocking cells C8-C15, and clicking
6              6                                 the Paste button.
7              7
8              8
9              9                                Notice also that the values in red are incorrect!!

45

5 = The Mean of X

Go to "F"
cting the mean from

C15 by clicking on the
C15, and clicking

red are incorrect!!
Calculating a standard deviation

Xi        Xi - Mean (Xi - Mean)2
-         -          -
1            -4
2             2                         The formulae in cells C8-C15 were created with
3             3                           relative addressing by the copy command.
4             4                         Hence copying each cell created increments its row numbers and
5             5                           column numbers as it copied the formula. This is fine for the
6             6                           +B7 becoming the +B8 in cell C8. But the cell reference for the
7             7                           Mean needs to remain constant as B19. This is accomplished by
8             8                           by changing the address for the Mean from B19 to \$B\$19 to indicat
9             9                           non-relative or constant addressing.
Simply hit the F4 key after entering the cell address
45                                        and Excel adds the \$.

5 = The Mean of X

Go to "G"
re created with

increments its row numbers and
he formula. This is fine for the
C8. But the cell reference for the
t as B19. This is accomplished by
e Mean from B19 to \$B\$19 to indicate

Calculating a standard deviation

Xi        Xi - Mean (Xi - Mean)2
-         -          -
1            -4                         Repeat sheet "E" with +B7-\$B\$19 formula.
2            -3                         Copy to cells C8 to C15
3            -2
4            -1
5             0
6             1
7             2
8             3
9             4

45

5 = The Mean of X                       Go To "H"
Calculating a standard deviation

Xi        Xi - Mean (Xi - Mean)2
-         -          -
1            -4        16               Square the deviation
2            -3         9               Copy the formula
3            -2         4
4            -1         1
5             0         0
6             1         1
7             2         4
8             3         9
9             4        16

45                      60              Calculate the sum of squared deviations

5 = The Mean of X

Go To "I"
Calculating a standard deviation

Xi          Xi - Mean (Xi - Mean)2
-           -          -
1           -4        16
2           -3         9
3           -2         4
4           -1         1
5            0         0
6            1         1
7            2         4
8            3         9
9            4        16

45                      60

5 = The Mean of X
2.738613 = the Standard Deviation             Calculate the sample st. dev. with the following formula:
"=SQRT(D17/8)"

Go To "Excel Function"
h the following formula:
This can of course all be done much easier

Xi                               Column1
-                                                      Simply block cells B7 to B15
1            Mean                         5   In the Menu above
2            Standard Error       0.912871       Click Tools > Data Analysis > Descriptive Stat
3            Median                       5   Put D5 for the Output Cell
4            Mode                   #N/A
5            Standard Deviation 2.738613      Click Finish
6            Sample Variance            7.5
7            Kurtosis                  -1.2
8            Skewness                     0   A bit easier, eh?
9            Range                        8
Minimum                      1
Maximum                      9
Sum                         45
Count                        9
2.105085
Confidence Level(95.0%)
Analysis > Descriptive Statistics > Next
Normal Curve               Kurtotic Distribution
St.dev                 1   St.dev                 1
Mean                   0   Mean                   0
xpi            6.28318
onehalf               -2

-4   #DIV/0!               -4    #DIV/0!
-3.9   #DIV/0!             -3.9    #DIV/0!
-3.8   #DIV/0!             -3.8    #DIV/0!
-3.7   #DIV/0!             -3.7    #DIV/0!
-3.6   #DIV/0!             -3.6    #DIV/0!
-3.5   #DIV/0!             -3.5    #DIV/0!
-3.4   #DIV/0!             -3.4    #DIV/0!
-3.3   #DIV/0!             -3.3    #DIV/0!
-3.2   #DIV/0!             -3.2    #DIV/0!
-3.1   #DIV/0!             -3.1    #DIV/0!
-3   #DIV/0!               -3    #DIV/0!
-2.9   #DIV/0!             -2.9    #DIV/0!
-2.8   #DIV/0!             -2.8    #DIV/0!
-2.7   #DIV/0!             -2.7    #DIV/0!
-2.6   #DIV/0!             -2.6    #DIV/0!
-2.5   #DIV/0!             -2.5    #DIV/0!
-2.4   #DIV/0!             -2.4    #DIV/0!
-2.3   #DIV/0!             -2.3    #DIV/0!
-2.2   #DIV/0!             -2.2    #DIV/0!
-2.1   #DIV/0!             -2.1    #DIV/0!
-2   #DIV/0!               -2    #DIV/0!
-1.9   #DIV/0!             -1.9    #DIV/0!
-1.8   #DIV/0!             -1.8    #DIV/0!
-1.7   #DIV/0!             -1.7    #DIV/0!
-1.6   #DIV/0!             -1.6    #DIV/0!
-1.5   #DIV/0!             -1.5    #DIV/0!
-1.4   #DIV/0!             -1.4    #DIV/0!
-1.3   #DIV/0!             -1.3    #DIV/0!
-1.2   #DIV/0!             -1.2    #DIV/0!
-1.1   #DIV/0!             -1.1    #DIV/0!
-1   #DIV/0!               -1    #DIV/0!
-0.9   #DIV/0!             -0.9    #DIV/0!
-0.8   #DIV/0!             -0.8    #DIV/0!
-0.7   #DIV/0!             -0.7    #DIV/0!
-0.6   #DIV/0!             -0.6    #DIV/0!
-0.5   #DIV/0!             -0.5    #DIV/0!
-0.4   #DIV/0!             -0.4    #DIV/0!
-0.3   #DIV/0!             -0.3    #DIV/0!
-0.2   #DIV/0!             -0.2    #DIV/0!
-0.1   #DIV/0!             -0.1    #DIV/0!
2.41E-15    #DIV/0!        2.41E-15     #DIV/0!
0.1   #DIV/0!              0.1    #DIV/0!
0.2   #DIV/0!              0.2    #DIV/0!
0.3   #DIV/0!              0.3    #DIV/0!
0.4   #DIV/0!   0.4   #DIV/0!
0.5   #DIV/0!   0.5   #DIV/0!
0.6   #DIV/0!   0.6   #DIV/0!
0.7   #DIV/0!   0.7   #DIV/0!
0.8   #DIV/0!   0.8   #DIV/0!
0.9   #DIV/0!   0.9   #DIV/0!
1   #DIV/0!     1   #DIV/0!
1.1   #DIV/0!   1.1   #DIV/0!
1.2   #DIV/0!   1.2   #DIV/0!
1.3   #DIV/0!   1.3   #DIV/0!
1.4   #DIV/0!   1.4   #DIV/0!
1.5   #DIV/0!   1.5   #DIV/0!
1.6   #DIV/0!   1.6   #DIV/0!
1.7   #DIV/0!   1.7   #DIV/0!
1.8   #DIV/0!   1.8   #DIV/0!
1.9   #DIV/0!   1.9   #DIV/0!
2   #DIV/0!     2   #DIV/0!
2.1   #DIV/0!   2.1   #DIV/0!
2.2   #DIV/0!   2.2   #DIV/0!
2.3   #DIV/0!   2.3   #DIV/0!
2.4   #DIV/0!   2.4   #DIV/0!
2.5   #DIV/0!   2.5   #DIV/0!
2.6   #DIV/0!   2.6   #DIV/0!
2.7   #DIV/0!   2.7   #DIV/0!
2.8   #DIV/0!   2.8   #DIV/0!
2.9   #DIV/0!   2.9   #DIV/0!
3   #DIV/0!     3   #DIV/0!
3.1   #DIV/0!   3.1   #DIV/0!
3.2   #DIV/0!   3.2   #DIV/0!
3.3   #DIV/0!   3.3   #DIV/0!
3.4   #DIV/0!   3.4   #DIV/0!
3.5   #DIV/0!   3.5   #DIV/0!
3.6   #DIV/0!   3.6   #DIV/0!
3.7   #DIV/0!   3.7   #DIV/0!
3.8   #DIV/0!   3.8   #DIV/0!
3.9   #DIV/0!   3.9   #DIV/0!
4   #DIV/0!     4   #DIV/0!

#DIV/0!

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 6 posted: 4/29/2010 language: English pages: 19