Microsoft Office Tutorials and References
In Depth Information
Deviating from the Middle
2. Calculate the mean and standard deviation.
See the section “Stuck in the Middle with AVERAGE, MEDIAN, and
MODE” to learn about the mean.
The STANDARDIZE function references these values. The mean is
calculated with the AVERAGE function, and the standard deviation is
calculated with the STDEV.P function. STDEV.P is used instead of STDEV.S
because the whole data population is used.
3. Place the cursor in the cell adjacent to the first data point entered
in Step 1.
4. Enter =STANDARDIZE( to start the function.
5. Click the cell that has the first data point.
6. Enter a comma ( ,).
7. Click the cell that has the mean.
8. Enter a comma ( ,).
9. Click the cell that has the standard deviation.
10. Enter a ) to end the function.
The formula with the STANDARDIZE function is now complete. However,
you must edit it to fix the references to the mean and standard
deviation. The references need to be made absolute so they won’t change
when the formula is dragged down to other cells.
11. Double-click the cell with the formula to enter the edit mode.
12. Precede the column and row parts of the reference to the cell that
contains the mean with a dollar sign ($).
13. Precede the column and row parts of the reference to the cell that
contains the standard deviation with a dollar sign ($).
14. Press the Enter key, the Tab key, or the Esc key to end the editing.
15. Use the fill handle to drag the formula down to the rest of the cells
that are adjacent to the source data points.
It’s important that the references to the mean and standard deviation are
treated as absolute references so they won’t change when the formula is
dragged to the other cells. Therefore, the formula should end up looking like
this: =STANDARDIZE(B7,$C$2,$C$3). Note the $ signs.