# Array Formula for Standard Deviation for Portfolio

#### mgirvin

##### Well-known Member
Dear Most Amazing Excel Team,

I have data for calculating the Standard Deviation for a Portfolio of stocks. My data is in cells A1 to E6 and looks like this:

....... Weight 0.3 0.4 0.3
....... P( ) E(R) A E(R) B E(R) C
Boom 0.15 0.3 0.45 0.33
Good 0.45 0.12 0.1 0.15
Poor 0.35 0.01 -0.15 -0.05
Bust 0.05 -0.2 -0.3 -0.09

My Array formula, that is entered in cell B8, looks like this:

{=SQRT(((SUM(C1:E1*C3:E3)-SUM(C3:E6*C1:E\$1*B3:B6))^2)*B3+((SUM(C1:E1*C4:E4)-SUM(C3:E6*C1:E1*B3:B6))^2)*B4+((SUM(C1:E1*C5:E5)-SUM(C3:E6*C1:E1*B3:B6))^2)*B5+((SUM(C1:E1*C6:E6)-SUM(C3:E6*C\$1:E\$1*B3:B6))^2)*B6)}

This works, but there must be a more elegant and compact way to do this?

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SQRT(SUM(((MMULT((C1:E1)*(C3:E6),TRANSPOSE(COLUMN(C1:E1)^0))-SUM((C3:E6)*(C1:E1)*(B3:B6)))^2)*(B3:B6)))

Hope this helps!

Last edited:
Dear Domenic,
I am honored that you posted such an amazing formula! Thank you very much!!!!!!!

It took me a while to post back because I had to remember my matrix algebra (lucky I keep all my old textbooks). I spent an hour doing some matrix algebra by hand so that I could get it down again.

{=SQRT(SUM(((MMULT((C1:E1)*(C3:E6),TRANSPOSE(COLUMN(C1:E1)^0))-SUM((C3:E6)*(C1:E1)*(B3:B6)))^2)*(B3:B6)))}

1) Did you use the COLUMN function so that if we inserted a column and add a new stock that the formula would update?
2) If I used the formula, { =SQRT(SUM((MMULT(C1:E1*C3:E6,TRANSPOSE(COLUMN(C1:E1)^0))-SUM(C3:E6*C1:E1*B3:B6))^2*B3:B6))}, without the parenthesis around the ranges of cells that are being multiplied, is there a disadvantage to this? I noticed in your formula that you had parenthesis around each range that was being multiplied.
3) Are you familiar with the MMULT function because you do matrix algebra or stock analysis on a regular basis?

You are awesome Domenic!!!!

Thank you very much!!!!!!!

You're very welcome! Thanks for the feedback!

1) Did you use the COLUMN function so that if we inserted a column and add a new stock that the formula would update?

The TRANSPOSE and COLUMN functions are used to return a vertical array of 1's so that MMULT could be exploited to return the desired outcome. So, therefore...

TRANSPOSE(COLUMN(C1:E1)^0)

...could easily be replaced by...

{1;1;1}

However, when the data spans a larger number of columns it's easier to reference the columns. And, yes, the range will automatically adjust when one or more columns are inserted.

2) If I used the formula, { =SQRT(SUM((MMULT(C1:E1*C3:E6,TRANSPOSE(COLUMN(C1:E1)^0))-SUM(C3:E6*C1:E1*B3:B6))^2*B3:B6))}, without the parenthesis around the ranges of cells that are being multiplied, is there a disadvantage to this? I noticed in your formula that you had parenthesis around each range that was being multiplied.

The parenthesis around each range is not needed. I only use them for aesthetic reasons.

3) Are you familiar with the MMULT function because you do matrix algebra or stock analysis on a regular basis?

None of the above... I've become familiar with MMULT by frequenting this Board and learning from others. Definitely an interesting function, one which can be used in a variety of ways.

Dear Domenic,

Thank you for helping me to learn so much!

Talk with you again soon!

Replies
5
Views
234
Replies
1
Views
105
Replies
19
Views
400
Replies
6
Views
878
Replies
4
Views
330

### Forum statistics

1,196,322
Messages
6,014,636
Members
441,832
Latest member
tony tessman ### 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