Open Excel and Enter Data

• From the Start Menu, open Excel
• Click in cell B1. Type in the word: Data
• Click in cell B2. Enter the data in Cells B2, B3, ..., B9.

• Click in the cell A1. Type in the word: Function
• Click in the cell A10. Type in the word: Sum
• Click in the cell B2. Holding down the mouse, drag down to cell B10, and the release the mouse button. The cells B2-B10 should be highlighted.
• Click on the symbol (autosum). You will see the total appear in Cell B10
• Click on the cell B10. Look at the formula given following the function symbol . The formula describes the function that Excel used to calculate the sum. The function is =SUM(B2:B9). This function sums the values in columns B2 through B9.

Var-sum.xls (Sheet: Sum)

Get the average

• Click in cell A11. Type in the word: Count
• Click in cell B11. Type in the formula: =count(b2:b9) , and then Enter. This formula counts the number of values in the cells in the range.
• Click in cell B12. Type in the formula: =b10/b11 , and then Enter. You will see the average appear in cell B12.
• Click in cell A13. Type in the word: Average
• Click in cell B13. Type in the formula: =AVERAGE(B2:B9), and then Enter. The Excel function AVERAGE will calculate the average of the values in the range of cells from B2 to B9.

Var-ave.xls

(Sheet: Average)

Construct a Column of Averages, and Differences

• Click in cell C1. Type in the word: Average
• Click in cell C2. Type in the formula: =$b$13 , and then Enter. The dollar signs in $b$13 tell Excel not to shift the location that is referred to when the formula is copied. The value of the mean will appear in cell C2
• Click in cell C2. Type CNTL-C to highlight the cell (copy it). Move the pointer to cell C3. Click and hold the left mouse button down, and highlight the cells C3 through C9. Release the mouse button and Enter. The formula should be copied, and the entries in the cell should be the average.
• Click in cell D1. Type in the word: Difference
• Click in cell D2. Type in the formula: =B2-C2 , and then Enter. The difference between the value in cell B2 and C2 will appear.
• Click in cell D2. Type CNTL-C to highlight the cell (copy it). Move the pointer to cell D3. Click and hold the left mouse button down, and highlight the cells D3 through D9. Release the mouse button and Enter. The formula should be copied, and the entries in the cell should be differences for each row. Unless a $ is included in the formula, the cells are 'relative to the position', so that the formula changes.

Var-dev.xls

(Sheet: Deviation)

Construct a column of squared deviations, and sums of squared deviations

• Click in cell E1. Type in the words: Sq. Dev.
• Click in cell E2. Type in the formula: =d2*d2 , and then Enter. The result is the square of the value in cell D2
• Click in cell E2. Type CNTL- C to highlight the cell (copy it). Click in cell E3. Click and hold the left mouse button down, and highlight the cells E3 through E9. Release the mouse button and Enter. The formula will be copied, and the entries in the cell will be squared differences.
• Click in cell E10. Type in the formula: =sum(e2:e9) , and then Enter. The total of the squared deviations will appear in cell E10

Var-sqdev.xls

(Sheet: Sq. Dev.)

Construct the sample variance

• Click in cell D10. Type in the word: Samp. Var
• Click in cell E11. Type in the formula: =e10/(b11-1) and then Enter. The sample variance will appear in cell E11
• Click in cell A14. Type in the word: Var
• Click in cell e14. Type in the formula: =var(b2:b9) and then Enter. The sample variance will appear in cell E14.
• Click in cell A15. Type in the work: Varp
• Click in cell E14. Type in the formula: =varp(b2:b9) and then Enter. The population variance will appear in cell E15.

Var-var.xls

(Sheet: Sq. Dev.)