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!