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.