Mean and Standard Deviation (SD) in Excel Worksheets

Enter Data

Format Headings

Calculate Sweetener Mass

Calculate Mean & SD

View Formulas

Format Signif Figures

Pink Packet Sweetener Experiment

This tutorial illustrates the calculation of mean and standard deviation, and formatting numbers to display correct significant figures. The PPSI assignment from Science 1 is used as an example.

The PPSI assignment asks for the mass of the sweetener in each sample to be determined by correcting for the mass of the packet. Then the mean (average) and standard deviation for the group of samples is to be computed. You are also asked to compare the pink product with the yellow or blue competitor. Two tables will be needed for the data: one for empty packet mass and a second for the full packet mass and calculation of sweetener mass.

This tutorial assumes that you are familiar with the material in the Data Tables tutorial.

Words in RED refer to menu or dialog box items, or words to enter. Top of page

Using the Images

The images below are shown at a reduced scale. Click on the Figure to jump to a full size picture. Then use the Back and Forward arrows on your browser to jump back and forth between this page and the image as often as needed.

Enter Data

Table I- Empty Packet Mass (put the title in row 1).
Enter the headings for three columns in row 2, Sample #, Packet P, Packet B or Y.

Table II - Sweetener Mass (put the title in row 1).
Enter the headings for five columns in row 2, Sample #, Full Pkt P, Swtner P, Full PktY or B, Swtner B or Y. Note that we are using abbreviations to fix the columns.

Enter the mass data for at least ten samples. Do not include the units (grams); Excel can't do math with words. Note that you do not need to type the zero before a decimal and that final zeros disappear if you enter them. We will fix this later. See the next step to deal with units.

In each table below the data, provide labeled rows for Mean and Std dev.
Top of page

Format Column Headings and Titles

The column widths should be uniform and the unit (grams) should be indicated. All this information for column headings should be placed in a single row using Text Wrap. Select row 2, where the column headings are found. Select the Format menu, then select Cells. In the dialog box, click the Alignment tab, change Horizontal to Center and in the Text Control section (lower left) click Wrap text, click OK. (see Figure 2).

Place the cursor on the line between row numbers 2 and 3 on the extreme left. Adjust the column height, when the double-headed arrow appears, drag the line down to enlarge row 2 to the height of three rows. Add the word (grams) to the heading where needed. Repeat these steps for Table II.

You can now spell out the words if you wish. In Table II, add (calculated) to the Sweetener columns. Set the column headings to bold. Adjust the column widths using the same method as for the column height.

Increase the font size for the titles. The result is shown in Figure 3. top of page

Fig

2

Fig

3

Calculate Mean and Standard Deviation

We are now ready to calculate the mean (average) and standard deviation for the empty packets. The example has 5 entries, you will need to adjust for your 10 data entries. Place the cursor in cell B8, where the value for the mean of the pink empty packets will go. Click on the Insert menu, select Function and click on Statistical in the left panel, then select Average in the right panel and click OK. The dialog box shows a range of B3:B7, which is correct so we click OK.

Place the cursor in cell B9, where the value for the STDEV of the pink empty packet masses will go and repeat the above procedural for the range B3:B7. You will need to correct the automatic range so you don't include the calculated mean along with the raw data in the range.

Erupt these steps for the blue or yellow data.
Top of page

Calculate Sweetener Mass

Columns C and E of Table II will hold the results of the calculated sweetener mass using the formula:

  • mass of sweetener = mass of full packet - mean mass of empty packets

This calculation is easily done using absolute reference to the value in Table I. Review the tutorial on this topic if needed. Place the cursor where the answer will go, C13 for the first value of pink sweetener, type or click the equal sign. Click on the value of the full packet in B13, type the - to subtract, click on the mean mass of the empty pink packets in B8 and press the F4 key to get absolute reference. Absolute reference is shown as $B$8 and means that B8 will not change as the formula is filled down, see Fig 5.

Fill down the rest of the column. Repeat these steps for the yellow or blue data. Note that the uniformity of the empty packet weights simplifies the experiment.

Calculate of mean and standard deviation of pink and yellow or blue sweetener, see Fig 6.
top of page

Fig

5

Fig

6

 

View Formulas to See What's Happening

You can view all the formulas in the worksheet as follows: Click the Tools menu, click Options at the bottom of the list, click the View tab. In the Window Options section, check Formulas. To fix any formula that is not correct, edit in the formula and click the green checkmark.

Review the formulas for the sweetener mass to see how relative referencing and absolute referencing ( with the $) work.

Top of page

 

Format Significant Figures

Note that calculated results like mean and standard deviation appear with too many decimal places and that final zeros do not appear. Number format will fix this. Click on column letters B:E, click Format, then click Cells. Click the Number tab and click on Number in the category list. Click the arrows to adjust the decimal places to 2. To place an emphasis on the final results, make the Mean and Std dev headings bold.


top of page

© Treva Pamer last revised February 8, 2003