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: Click here! Note the cell address (A:C26), and the text in the grey Edit Bar ("Click here!") 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. Click here And here also And Here And finally here. 5 Now click on these numbers. 5 2 Notice that to add two numbers, simply add their cell addresses. 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 dd their cell addresses. 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 ng the cell address 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 |

OTHER DOCS BY decree

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.