EXCEL

NORMAL  DISTRIBUTION

 

 

 

 

 

 

 

 

 

 

Standardize Using EXCEL

The standardizing function will return a standardized value for a specified x, mean,

and standard deviation.  Use the formula  =STANDARDIZE(x,mean,standard_dev)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The data given below represents a random sample of shoe sizes of 10 college students.

The mean of the distribution is 8.9 and the standard deviation is 2.23.  Find the

standardized values.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

DATA

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Row

Column A

Column B

 

 

 

 

 

 

 

1

Shoe Size

z-value

 

 

 

 

 

 

 

2

7

 

 

 

 

 

 

 

 

3

8

 

 

 

 

 

 

 

 

4

10

 

 

 

 

 

 

 

 

5

6

 

 

 

 

 

 

 

 

6

12

 

 

 

 

 

 

 

 

7

13

 

 

 

 

 

 

 

 

8

8

 

 

 

 

 

 

 

 

9

9

 

 

 

 

 

 

 

 

10

9

 

 

 

 

 

 

 

 

11

7

 

 

 

 

 

 

 

 

12

Mean

8.9

 

 

 

 

 

 

 

13

Std. Dev.

2.23

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Standardize each value!

 

 

 

 

 

1.  Activate the cell corresponding to the z-score of shoe size 7 which is B2.

 

 

 

 

 

 

 

 

 

 

2.  Get one standardized value: Type the following formula in the formula bar:

     =STANDARDIZE(7,8.9,2.23) and press Enter.

     You will see the standardized value below.

 

Row

Column A

Column B

 

 

 

 

 

 

 

1

Shoe Size

z-value

 

 

 

 

 

 

 

2

7

-0.85202

 

 

 

 

 

 

 

3

8

 

 

 

 

 

 

 

 

4

10

 

 

 

 

 

 

 

 

5

6

 

 

 

 

 

 

 

 

6

12

 

 

 

 

 

 

 

 

7

13

 

 

 

 

 

 

 

 

8

8

 

 

 

 

 

 

 

 

9

9

 

 

 

 

 

 

 

 

10

9

 

 

 

 

 

 

 

 

11

7

 

 

 

 

 

 

 

 

12

Mean

8.9

 

 

 

 

 

 

 

13

Std. Dev.

2.23

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. Get standardized values for all cells.

 For x: Enter cell of first observation A2.

 For Mean: Enter $B$12 which is the cell address of mean.

                 The dollar signs make this an absolute cell address so that it will not change when   

                  copied.

 For Standard Deviation: Enter the absolute cell address for the standard deviation $B$13, so that

it will not change when copied.

Input:  =STANDARDIZE(A2,$B$12,$B$13)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4.  Get remaining z-values: Use the copy command, go to Edit and Fill Down.

 

 

 

 

 

 

 

 

 

 

 

Row

Column A

Column B

 

 

 

 

 

 

 

1

Shoe Size

z-value

 

 

 

 

 

 

 

2

7

-0.852018

 

 

 

 

 

 

 

3

8

-0.403587

 

 

 

 

 

 

 

4

10

0.493274

 

 

 

 

 

 

 

5

6

-1.300448

 

 

 

 

 

 

 

6

12

1.390135

 

 

 

 

 

 

 

7

13

1.838565

 

 

 

 

 

 

 

8

8

-0.403587

 

 

 

 

 

 

 

9

9

0.044843

 

 

 

 

 

 

 

10

9

0.044843

 

 

 

 

 

 

 

11

7

-0.852018

 

 

 

 

 

 

 

12

Mean

8.9

 

 

 

 

 

 

 

13

Std. Dev.

2.23

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The table above contains all the standardized values. 

 

copyright 2004 Elisabeth Knowlton