Mean and Standard Deviation (SD) in
Excel Worksheets
|
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
