Adding z scores (using sum and standardize functions)

[qwerty13]

Hi,

I have about 25 columns and 116 rows all with numbers in them. For each row, i want to add up the z scores for each respective column. I already calculated the average and standard deviation at the bottom of each column. I've tried multiple ways but nothing seems to work. Any help would be appreciated!

 

[Andrew Poulsom]

Welcome to MrExcel.

I'm no statistician, but wouldn't the sum of the z scores be zero? Example:

=SUM(STANDARDIZE((A1:A10),A11,A12))

confirmed with Ctrl+Shift+Enter.

 

[qwerty13]

I'm not adding the zscores all from the same column, rather I'm adding 1 zscore from each column

For example, this is what I tried

=sum(standardize(g2, g118, g119):standardize(ae2, ae118, ae119))

 

[Andrew Poulsom]

Surprisingly this worked for me:

=SUM(STANDARDIZE((A1:C1),(A$11:C$11),(A$12:C$12)))

confirmed with Ctrl+Shift+Enter. Adjust the references to suit.

 

[qwerty13]

After using the ctrl+shift+enter this worked perfectly! Thanks!

One follow up,

Now if I want to multiply each z score by a number, say in row 13 with reference to your formula, how would I adjust it?

I know I would use sumproduct but where would I place the extra data?

Thanks

 

[Andrew Poulsom]

Like this?

=SUMPRODUCT(STANDARDIZE((A1:C1),(A$11:C$11),(A$12:C$12)),A$13:C$13)