# Adding z scores (using sum and standardize functions)

#### qwerty13

##### New Member
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!

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Andrew Poulsom

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

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

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

Last edited:

#### Andrew Poulsom

##### MrExcel MVP
Like this?

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

Replies
5
Views
212
Replies
9
Views
2K
Replies
4
Views
100
Replies
0
Views
118
Replies
13
Views
394

1,195,625
Messages
6,010,760
Members
441,568
Latest member
abbyabby

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back