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?
anybody know.. if you already have a mutual funds' 3 or 5-year standard deviation, how to calculate the overall portfolio's standard deviation?
Hi guys,
I need to calculate standard deviation for a portfolio with 31 stock. I have a column with the stock names (column B), their mean return (column F), standard deviation (column G) and 31*31 correlation matrix.
Is there a convenient way to calculate this stuff?
Thanks a lot in advance!
I need help on the excel formular for the standard deviation of a 3 asset portfolio. I tried it but got an error message.
3D bar chart:
Column 1 has labels
Column 2 has data
Column 3 has calculated Standard Deviation
I need to show one cell from column three as a standard deviation on one of
the data items in Column 2. Can this be done?
I am looking for a formula in Excel to Calculate the standard deviation of NPV (Net Present Value)
Your assistance will be most appreciated
Regards
Howard
I am looking for a way to calculate the average 6 month Standard Deviations (Cell C22) using only column B. I have tried the formula in C26 to no avail, any suggestions would be greatly appreciated.
******** ******************** ************************************************************************>
Microsoft Excel - Excel Question 2(047)16
___Running: xl2000 : OS = Windows ME
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
C12
C13
C14
C15
C16
C17
C18
C22
C26
=
A
B
C
D
E
1
2
Rolling
3
6 Month
4
Standard
5
Series
Deviations
6
of Col B
7
1
-10.65%
8
2
8.76%
9
3
9.57%
10
4
5.59%
11
5
0.20%
12
6
7.56%
7.03%
13
7
3.61%
3.22%
14
8
3.73%
3.01%
15
9
-1.86%
3.16%
16
10
6.00%
3.21%
17
11
1.93%
3.00%
18
12
-3.60%
3.34%
19
20
Average of
21
Column C
22
3.71%
23
24
25
26
5.58%
Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have entered the exact same formula into two different cells in the same worksheet and each yields a different result...
Cell J21 contains the formula {=STDEVP(IF(C2:C21<>0,C2:C21,""))} and yields a result of 349.38
Cell V21 contains the formula {=STDEVP(IF(C2:C21<>0,C2:C21,""))} and yields a result of 0.00
Any ideas on what could possibly be going on here?
List of 680 Serivces organized by price with varying volume.
I need to find a way to group the services into 12-15 tiers that will allow me to ramain revenue neutral and have smallest standard deviation. Considering price and volume.
I need each tier to have one price and stay revenue neutral.
Below is a list of my services.
Service 1
133
2,270.72
Service 2
9
2,270.72
Service 3
1
2,301.28
Service 4
2
2,307.55
Service 5
1
2,310.40
Service 6
16
2,315.19
Service 7
87
5,717.29
Service 8
8
5,731.39
Service 9
65
5,744.48
Service 10
4
5,754.49
Service 11
13
5,790.59
Service 12
1
5,830.70
Service 13
2
5,837.13
Service 14
1
5,947.24
Service 15
3
5,992.58
Service 16
6
6,231.63
Service 17
16
6,270.34
Service 18
15
6,314.17
Service 19
2
6,343.68
Service 20
2
6,379.75
Service 21
4
6,460.18
Service 22
1
6,497.46
Service 23
166
6,508.96
Service 24
82
14,235.24
Service 25
1
14,591.90
Service 26
10
15,890.47
Service 27
1
16,657.83
Service 28
28
17,480.83
Service 29
7
17,561.94
Service 30
42
17,734.95
Service 31
2
18,027.35
Service 32
2
18,273.10
Service 33
4
18,596.25
Service 34
6
18,608.20
Service 35
2
18,734.85
Service 36
8
19,807.35
Service 37
1
19,984.30
Service 38
6
20,412.68
Service 39
1
22,806.20
Service 40
11
22,879.97
Service 41
1
29,068.60
Service 42
1
37,797.00
I face problem using std deviation formula : =stdev(cell no.X:cell no.Y) .
When i key-in the same value, which should be no std deviation, but when using excel formula of std deviation, it gives me value for std deviation.
Pls help.
