The QA column of the current issue of IR Watch Newsletter has a great question that might help IR, CS, and stats students.

Q: Centering Data with Excel– In Excel, how do you center a data set?

 A: To center a data set, use the STANDARDIZE function which converts x values into z-scores; i.e.

z = (x – a)/s

where a and s respectively are the population arithmetic mean and standard deviation. The following table emulates an Excel spreadsheet.

 

A

B

C

1

Age, x(A)

Weight, x(W)

Height, x(H)

2

64

57

8

3

71

59

10

4

53

49

6

5

67

62

11

6

55

51

8

7

58

50

7

8

77

55

10

9

57

48

9

10

56

42

10

11

51

42

6

12

76

61

12

13

68

57

9

14

     

15

z(A)

z(W)

z(H)

16

0.14

0.62

-0.44

17

0.92

0.92

0.61

18

-1.09

-0.55

-1.49

19

0.47

1.36

1.14

20

-0.86

-0.26

-0.44

21

-0.53

-0.40

-0.97

22

1.59

0.33

0.61

23

-0.64

-0.70

0.09

24

-0.75

-1.58

0.61

25

-1.31

-1.58

-1.49

26

1.47

1.21

1.67

27

0.58

0.62

0.09

Rows 2 – 13 contains the data set x(A), x(W), and x(H). In rows 16 – 27 the set was centered by typing in cell A16 the formula

 =STANDARDIZE(A2,AVERAGE(A$2:A$13),STDEV(A$2:A$13))

 Pasting this formula in cells A16 through C27 centers the data set. That was easy!