
Standard Deviation++ In Excel


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Hi there, not sure if this is the right forum for this post, but anyway, here it goes.
I have a column of historical 5year returns. For this data I need the standard deviation of the annualized results (that is, the 5year returns raised to the power of 1/5). Is it possible to do this directly with the Stdev formula in Excel, or do I have to make a separate column for the annualized returns?
Regards
Similar Excel Video Tutorials
Beginning Stock Analysis
 See how to create array formulas for Portfolio Return & Standard Deviation. See how to calculate individual stock return and standard deviation gi ...
Similar Topics
Hi,
Im scratching my head on this one... I want to be able to calculate the geomean and (geo)stdev for numbers in a 2x2 array.
I've got the geo mean figured out, but Im confused about the stdev... here's what Im doing:
Code:
'Calculate 1YR Return Series
For y = 1 To numFunds
One_Year = 1
If numMonths > 11 Then
For x = numMonths  11 To numMonths
One_Year = One_Year * ReturnSeries(x, y)
Next x
MeanSeries(y) = One_Year ^ (1 / 12)
'Annualize the GeoMean
MeanSeries(y) = MeanSeries(y) ^ 12  1
Worksheets("test").Cells(6, y + 1) = MeanSeries(y)
End If
Next y
How would I go about calculating the standard deviation for all the returns in a column, and then moving to the next column, etc... (ie., calculate standard deviation in column B, then calculate it for Column C, ..., calculate stdev in column 'numFunds')?
BTW, the way Im calculating the geometric standard deviation is:
[power(10, stdev(range))1]*sqrt(12)
Hi,
Would someone be able to help me with standard deviation within excel? I have used the formula =STDEV(number one, number two...etc). I think the problem is that my data is not a normal distribution, thus the incorrect standard deviation value. But I need to some how calculate standard deviation? Is there a way that I can with excel? I have over 20 000 data elements, thus making it harder to work out. Could someone please suggest some ideas that I could possibly implement to help correctly calculate standard deviation?
I hope I am posting in the right section
I have a column of numbers and I need to find 1 standard deviation above and 1 standard deviation below
I know how to find the normal standard deviation in excel [STDEV(A1:A100)] but how do I go about finding the 1 above and 1 below? Is there a forumla for this in excel?
thanks a lot guys
Hi guys,
I'm trying to use stdev but for certain groups of values which should return 0 it doesn't.
e.g. rows 4  10 all have value 191.89 yet the stdev returns 3.0699E14
I have tried it with both =STDEV in the worksheet and in worksheetfunction.stdev in VBA and still returns an undesired result.
Can anyone shed some light on where I'm going wrong?
Cheers,
Will
Hello,
Can someone please help me with my query?
I have a large database and would like to calculate standard deviation based on a condition "Yes" or "No".
Shown below is a small layout from my database:
Criteria
Yes
Yes
No
No
Yes
Yes
No
No
Yes
Yes
No
No
Yes
Yes
No
No
Yes
Yes
No
No
Yes
Yes
No
No
Value
37.00
28.50
35.40
25.00
27.90
32.80
37.50
27.60
28.90
29.90
30.30
26.10
27.40
23.50
29.10
29.20
33.80
40.40
30.50
37.90
26.50
Manual Calculation Method:
Standard Deviation (All)
4.65
37.00
28.50
35.40
25.00
27.90
32.80
37.50
27.60
28.90
29.90
30.30
26.10
27.40
23.50
29.10
29.20
33.80
40.40
30.50
37.90
26.50
Standard Deviation (Yes)
2.96
37.00
28.50
27.90
32.80
28.90
29.90
26.10
27.40
29.10
29.20
30.50
Standard Deviation (No)
6.00
35.40
25.00
37.50
27.60
30.30
23.50
33.80
40.40
37.90
26.50
STDEV (No)
6.00
Conditional Calculation Method:
Standard Deviation (No)
6.31
The equation in cell B10 does not give the correct answer. The correct answer is in cell B7.
Note:
This is an example of my database. In the future, I will add more data columns, hence the formula looks upto column IV.
In cell B10, I am using the following array formula to calculate the standard deviation: "{=STDEV(($D$1:$IV$1="No")*(D2:IV2))}".
Please note that in some instances, the cell in the "Value" row will be blank. Again, I have shown this above.
Please can someone help me to obtain the correct answer of "6.00" and not "6.31" as calculated by the array formula.
What change(s) do I need to make to my array formula?
Many thanks.
Hello,
I have a dynamic range that I need to be able to calculate the standard deviation for. The dynamic range sometimes has blank cells and #N/A cells which I do not want included in the calculation. However, there are sometimes cells with the number 0 in it (instead of blank) which I do want to include. I have come up with an array formula, but the standard deviation it calculates is not correct. The formula is:
=STDEV(IF(ISNUMBER(A3:A20000)*ISNUMBER(A3:A20000),1,0))
*This is an array formula calculated by pressing CTRL+SHIFT+ ENTER
The number it calculates is 0.43, however, this is incorrect. If you just calculate the standard deviation of the actual data points in excel (cells A138:A5055 in this case) : STDEV(A138:A5055), it returns 0.17. This is the correct standard deviation. Could someone please help me with this?
Kind Regards,
Maani
http://www.4shared.com/file/12409801...c74/Book1.html
Hi,
I'm looking to calculate the standard deviation for various rolling time periods each month. I have used formulas to link rates of returns (through help from the Excel Forum) for the last "n" values in a column excluding blanks, but I could not adapt that formula for this purpose. The previously provided formula is on the tab "Rates of Return" in the attached file.
The results I'm looking for (in column F) on the tab "Standard Deviation".
Any help would be appreciated.
Thanks,
Phillycheese
Anyone know how to write the formula in VB for standard deviation?
StDev(p22,p23,p24) Dosen't work
XlStDev Not sure what that is
stddev nope
???????????????????
I have no problems with standard deviation in Excel.
Thanks again
I'm having problems with my 'if' formula with regards to stdev. I created need excel to calculate the standard deviation of certain rows in a column. The rows I would like to have included in the standard deviation calculation are indicated in column C with a "1", and the values in column J. All other rows (that I do not want in the calculation) in column C are blank. All data is in a separate worksheet named Transcription Data. Here is the formula I wrote: =IF('Transcription Data'!c3:c300, ">0", STDEV('Transcription Data'!J3:J300)) However, the formula keeps calculating the standard deviation of the entire J column. What am I doing wrong? I've also tried =STDEV(IF(c3:c300, ">0", J3:J300))
Any suggestions?
Hi, I've already learned a lot from reading this site so I'm hoping you can help me with my particular question. I have a large population of hourly temperature data. The first column is the date, formatted MM/DD/YY HH:MM; the second column is temperature. I have 5 years of data. Currently each year is a separate worksheet but that can be changed as needed.
What I need is a formula that will report the standard deviation for each day of the year, across all years. So, stdev of the population(1/1/05,1/1/06,1/1/07,etc).
I tried some fiddling with If functions to narrow down the range, but stdev is tricky because it isn't additive in the traditional sense. If I can't do this with only one function, other ideas how I might arrange my spreadsheet to calculate it manually would be appreciated.
I'm trying to make some pretty straightforward column charts and then add error bars for standard deviation on the charts, but I can't get it to do the standard deviation for each individual column of data. I can only get all of the columns to use the same standard deviation (whichever one i choose as my custom value).
Anyways I am able to set each bar to have a unique standard deviation error bar?
Using Excel 2007 btw.
Thanks
This is going to be a little hard to explain but I'll try:
I've got two worksheets, A and B.
A has the raw data and has several columns including:
Date (all days from 2000  2008)
Company Codes (e.g. BHP, RIO, etc)
Daily return
Standard deviation of daily returns (which is calculated by stdev)
Worksheet B is the worksheet i'm working on and has the following:
Date (specific dates)
Company Codes
Standard deviation of daily returns
Since worksheet B only has specific dates and not the full range of dates in worksheet A, what I want to know is if it is possible to tell excel to copy the standard deviation of the daily return from worksheet A to worksheet B (just the value, not the formula) and only copy those values for those dates that are in worksheet B.
Thanks
Hi,
I am trying to compute the monthly standard deviation from the daily returns across large data of a stock. Can anyone help me with the vba code to calculate automatically. I want the vba macro not the functions for the project.
Thanks
I’m trying to find a formula that can funnel the selected values from a larger table to a smaller table. In doing so I will then be able to take the new table values and analyse them further.
The hitch is; the required values must be able to change according to the selections that are ticked (and given a number value), with the value that matches the intersection of the row and column on the bigger table.
*
B
C
D
E
F
G
H
I
3
*
*
*
*
*
Chosen Stock Selections
4
Stock Symbol
Standard Deviation
Average Returns
Selected
0
Stock Symbol
Standard Deviation
Average Returns
5
GS
1.81%
5.00%
FALSE
*
*
*
*
6
DIS
1.36%
5.00%
TRUE
1
DIS
0.0135724
0.05
7
MRO
1.99%
5.00%
TRUE
2
MRO
0.0199118
0.05
8
NOC
1.08%
5.00%
TRUE
3
NOC
0.0107916
0.05
9
COP
1.65%
5.00%
FALSE
*
*
*
*
10
ALL
1.16%
5.00%
FALSE
*
*
*
*
11
DOW
1.46%
5.00%
FALSE
*
*
*
*
12
VZ
1.19%
5.00%
FALSE
*
*
*
*
13
T
1.27%
5.00%
TRUE
4
T
0.0126745
0.05
14
PFE
1.34%
5.00%
TRUE
5
PFE
0.0134479
0.05
15
AMP
2.13%
5.00%
FALSE
*
*
*
*
16
SAF
1.80%
5.00%
TRUE
6
SAF
0.0179849
0.05
17
CINF
1.35%
5.00%
TRUE
7
CINF
0.0135432
0.05
18
MDP
1.28%
5.00%
FALSE
*
*
*
*
19
EXPE
2.38%
5.00%
FALSE
*
*
*
*
20
TMK
1.07%
5.00%
TRUE
8
TMK
0.0106544
0.05
21
JAVA
8.83%
5.00%
FALSE
*
*
*
*
22
SUN
2.27%
5.00%
FALSE
*
*
*
*
23
AIZ
1.46%
5.00%
TRUE
9
AIZ
0.0145676
0.05
24
KG
2.44%
5.00%
FALSE
*
*
*
*
Spreadsheet Formulas
Cell
Formula
C4
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
B5
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C5
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F5
=IF(G5="","",LOOKUP(9.99999999999999E+307,F$4:F4)+1)
G5
=IF(E5=FALSE,"",B5)
H5
=IF(E5=TRUE,C5,"")
I5
=IF(E5=TRUE,D5,"")
B6
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C6
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F6
=IF(G6="","",LOOKUP(9.99999999999999E+307,F$4:F5)+1)
G6
=IF(E6=FALSE,"",B6)
H6
=IF(E6=TRUE,C6,"")
I6
=IF(E6=TRUE,D6,"")
B7
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C7
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F7
=IF(G7="","",LOOKUP(9.99999999999999E+307,F$4:F6)+1)
G7
=IF(E7=FALSE,"",B7)
H7
=IF(E7=TRUE,C7,"")
I7
=IF(E7=TRUE,D7,"")
B8
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C8
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F8
=IF(G8="","",LOOKUP(9.99999999999999E+307,F$4:F7)+1)
G8
=IF(E8=FALSE,"",B8)
H8
=IF(E8=TRUE,C8,"")
I8
=IF(E8=TRUE,D8,"")
B9
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C9
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F9
=IF(G9="","",LOOKUP(9.99999999999999E+307,F$4:F8)+1)
G9
=IF(E9=FALSE,"",B9)
H9
=IF(E9=TRUE,C9,"")
I9
=IF(E9=TRUE,D9,"")
B10
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C10
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F10
=IF(G10="","",LOOKUP(9.99999999999999E+307,F$4:F9)+1)
G10
=IF(E10=FALSE,"",B10)
H10
=IF(E10=TRUE,C10,"")
I10
=IF(E10=TRUE,D10,"")
B11
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C11
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F11
=IF(G11="","",LOOKUP(9.99999999999999E+307,F$4:F10)+1)
G11
=IF(E11=FALSE,"",B11)
H11
=IF(E11=TRUE,C11,"")
I11
=IF(E11=TRUE,D11,"")
B12
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C12
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F12
=IF(G12="","",LOOKUP(9.99999999999999E+307,F$4:F11)+1)
G12
=IF(E12=FALSE,"",B12)
H12
=IF(E12=TRUE,C12,"")
I12
=IF(E12=TRUE,D12,"")
B13
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C13
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F13
=IF(G13="","",LOOKUP(9.99999999999999E+307,F$4:F12)+1)
G13
=IF(E13=FALSE,"",B13)
H13
=IF(E13=TRUE,C13,"")
I13
=IF(E13=TRUE,D13,"")
B14
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C14
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F14
=IF(G14="","",LOOKUP(9.99999999999999E+307,F$4:F13)+1)
G14
=IF(E14=FALSE,"",B14)
H14
=IF(E14=TRUE,C14,"")
I14
=IF(E14=TRUE,D14,"")
B15
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C15
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F15
=IF(G15="","",LOOKUP(9.99999999999999E+307,F$4:F14)+1)
G15
=IF(E15=FALSE,"",B15)
H15
=IF(E15=TRUE,C15,"")
I15
=IF(E15=TRUE,D15,"")
B16
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C16
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F16
=IF(G16="","",LOOKUP(9.99999999999999E+307,F$4:F15)+1)
G16
=IF(E16=FALSE,"",B16)
H16
=IF(E16=TRUE,C16,"")
I16
=IF(E16=TRUE,D16,"")
B17
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C17
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F17
=IF(G17="","",LOOKUP(9.99999999999999E+307,F$4:F16)+1)
G17
=IF(E17=FALSE,"",B17)
H17
=IF(E17=TRUE,C17,"")
I17
=IF(E17=TRUE,D17,"")
B18
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C18
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F18
=IF(G18="","",LOOKUP(9.99999999999999E+307,F$4:F17)+1)
G18
=IF(E18=FALSE,"",B18)
H18
=IF(E18=TRUE,C18,"")
I18
=IF(E18=TRUE,D18,"")
B19
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C19
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F19
=IF(G19="","",LOOKUP(9.99999999999999E+307,F$4:F18)+1)
G19
=IF(E19=FALSE,"",B19)
H19
=IF(E19=TRUE,C19,"")
I19
=IF(E19=TRUE,D19,"")
B20
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C20
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F20
=IF(G20="","",LOOKUP(9.99999999999999E+307,F$4:F19)+1)
G20
=IF(E20=FALSE,"",B20)
H20
=IF(E20=TRUE,C20,"")
I20
=IF(E20=TRUE,D20,"")
B21
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C21
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F21
=IF(G21="","",LOOKUP(9.99999999999999E+307,F$4:F20)+1)
G21
=IF(E21=FALSE,"",B21)
H21
=IF(E21=TRUE,C21,"")
I21
=IF(E21=TRUE,D21,"")
B22
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C22
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F22
=IF(G22="","",LOOKUP(9.99999999999999E+307,F$4:F21)+1)
G22
=IF(E22=FALSE,"",B22)
H22
=IF(E22=TRUE,C22,"")
I22
=IF(E22=TRUE,D22,"")
B23
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C23
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F23
=IF(G23="","",LOOKUP(9.99999999999999E+307,F$4:F22)+1)
G23
=IF(E23=FALSE,"",B23)
H23
=IF(E23=TRUE,C23,"")
I23
=IF(E23=TRUE,D23,"")
B24
{=TRANSPOSE('5 Year Returns and EPS'!AT4:BM4)}
C24
{=TRANSPOSE('5 Year Returns and EPS'!AS12:BM12)}
F24
=IF(G24="","",LOOKUP(9.99999999999999E+307,F$4:F23)+1)
G24
=IF(E24=FALSE,"",B24)
H24
=IF(E24=TRUE,C24,"")
I24
=IF(E24=TRUE,D24,"")
Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!
The selected data is transferred to a compact table (thanks Peter!!)
*
A
B
C
26
9
*
Average
27
Inputs
STDEV
returns
28
DIS
1.36%
100.00%
29
MRO
1.99%
200.00%
30
NOC
1.08%
300.00%
31
T
1.27%
400.00%
32
PFE
1.34%
500.00%
33
SAF
1.80%
600.00%
34
CINF
1.35%
700.00%
35
TMK
1.07%
800.00%
36
AIZ
1.46%
900.00%
37
Average
1.4%
500.0%
Spreadsheet Formulas
Cell
Formula
A26
=LOOKUP(9.99999999999999E+307,F5:F24)
A28
=IF(ROWS(A$28:A28)>$A$26,"",INDEX(G$5:G$24,MATCH(ROWS(A$28:A28),$F$5:$F$24,0)))
B28
=IF(ROWS(B$28:B28)>$A$26,"",INDEX(H$5:H$24,MATCH(ROWS(B$28:B28),$F$5:$F$24,0)))
C28
=IF(ROWS(C$28:C28)>$A$26,"",INDEX(F$5:F$24,MATCH(ROWS(C$28:C28),$F$5:$F$24,0)))
A29
=IF(ROWS(A$28:A29)>$A$26,"",INDEX(G$5:G$24,MATCH(ROWS(A$28:A29),$F$5:$F$24,0)))
B29
=IF(ROWS(B$28:B29)>$A$26,"",INDEX(H$5:H$24,MATCH(ROWS(B$28:B29),$F$5:$F$24,0)))
C29
=IF(ROWS(C$28:C29)>$A$26,"",INDEX(F$5:F$24,MATCH(ROWS(C$28:C29),$F$5:$F$24,0)))
A30
=IF(ROWS(A$28:A30)>$A$26,"",INDEX(G$5:G$24,MATCH(ROWS(A$28:A30),$F$5:$F$24,0)))
B30
=IF(ROWS(B$28:B30)>$A$26,"",INDEX(H$5:H$24,MATCH(ROWS(B$28:B30),$F$5:$F$24,0)))
C30
=IF(ROWS(C$28:C30)>$A$26,"",INDEX(F$5:F$24,MATCH(ROWS(C$28:C30),$F$5:$F$24,0)))
A31
=IF(ROWS(A$28:A31)>$A$26,"",INDEX(G$5:G$24,MATCH(ROWS(A$28:A31),$F$5:$F$24,0)))
B31
=IF(ROWS(B$28:B31)>$A$26,"",INDEX(H$5:H$24,MATCH(ROWS(B$28:B31),$F$5:$F$24,0)))
C31
=IF(ROWS(C$28:C31)>$A$26,"",INDEX(F$5:F$24,MATCH(ROWS(C$28:C31),$F$5:$F$24,0)))
A32
=IF(ROWS(A$28:A32)>$A$26,"",INDEX(G$5:G$24,MATCH(ROWS(A$28:A32),$F$5:$F$24,0)))
B32
=IF(ROWS(B$28:B32)>$A$26,"",INDEX(H$5:H$24,MATCH(ROWS(B$28:B32),$F$5:$F$24,0)))
C32
=IF(ROWS(C$28:C32)>$A$26,"",INDEX(F$5:F$24,MATCH(ROWS(C$28:C32),$F$5:$F$24,0)))
A33
=IF(ROWS(A$28:A33)>$A$26,"",INDEX(G$5:G$24,MATCH(ROWS(A$28:A33),$F$5:$F$24,0)))
B33
=IF(ROWS(B$28:B33)>$A$26,"",INDEX(H$5:H$24,MATCH(ROWS(B$28:B33),$F$5:$F$24,0)))
C33
=IF(ROWS(C$28:C33)>$A$26,"",INDEX(F$5:F$24,MATCH(ROWS(C$28:C33),$F$5:$F$24,0)))
A34
=IF(ROWS(A$28:A34)>$A$26,"",INDEX(G$5:G$24,MATCH(ROWS(A$28:A34),$F$5:$F$24,0)))
B34
=IF(ROWS(B$28:B34)>$A$26,"",INDEX(H$5:H$24,MATCH(ROWS(B$28:B34),$F$5:$F$24,0)))
C34
=IF(ROWS(C$28:C34)>$A$26,"",INDEX(F$5:F$24,MATCH(ROWS(C$28:C34),$F$5:$F$24,0)))
A35
=IF(ROWS(A$28:A35)>$A$26,"",INDEX(G$5:G$24,MATCH(ROWS(A$28:A35),$F$5:$F$24,0)))
B35
=IF(ROWS(B$28:B35)>$A$26,"",INDEX(H$5:H$24,MATCH(ROWS(B$28:B35),$F$5:$F$24,0)))
C35
=IF(ROWS(C$28:C35)>$A$26,"",INDEX(F$5:F$24,MATCH(ROWS(C$28:C35),$F$5:$F$24,0)))
A36
=IF(ROWS(A$28:A36)>$A$26,"",INDEX(G$5:G$24,MATCH(ROWS(A$28:A36),$F$5:$F$24,0)))
B36
=IF(ROWS(B$28:B36)>$A$26,"",INDEX(H$5:H$24,MATCH(ROWS(B$28:B36),$F$5:$F$24,0)))
C36
=IF(ROWS(C$28:C36)>$A$26,"",INDEX(F$5:F$24,MATCH(ROWS(C$28:C36),$F$5:$F$24,0)))
B37
=AVERAGE(B28:B36)
C37
=AVERAGE(C28:C36)
The next table needs to find the intersecting values for the "selections" in the 20 x 20 table and place them into the smaller table
From this table
*
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
1280
*
*
GS
DIS
MRO
NOC
COP
ALL
DOW
VZ
T
PFE
AMP
SAF
CINF
MDP
EXPE
TMK
JAVA
SUN
AIZ
KG
1281
*
0
1
2
*
3
*
*
4
*
5
*
*
6
*
*
7
*
*
8
9
*
1282
GS
1
100.00%
46.70%
29.79%
34.14%
28.36%
52.47%
47.91%
43.25%
41.47%
35.66%
65.32%
30.19%
55.26%
32.81%
30.88%
56.97%
11.14%
28.52%
36.51%
18.20%
1283
DIS
2
46.70%
100.00%
18.91%
28.89%
20.85%
37.38%
42.06%
37.38%
35.59%
32.86%
49.33%
23.59%
38.60%
34.41%
26.57%
40.57%
4.04%
20.94%
29.40%
17.68%
1284
MRO
*
29.79%
18.91%
100.00%
20.03%
72.68%
23.49%
31.61%
17.73%
21.37%
19.35%
32.06%
10.44%
22.31%
13.59%
17.91%
21.07%
2.01%
64.18%
19.23%
15.13%
1285
NOC
3
34.14%
28.89%
20.03%
100.00%
20.31%
31.47%
32.45%
29.19%
29.63%
26.88%
36.37%
17.79%
33.42%
29.25%
16.45%
36.04%
3.75%
17.95%
19.82%
16.34%
1286
COP
*
28.36%
20.85%
72.68%
20.31%
100.00%
22.48%
31.92%
22.94%
24.46%
18.93%
31.58%
13.17%
18.60%
11.32%
12.91%
22.16%
3.41%
63.30%
18.58%
11.51%
1287
ALL
*
52.47%
37.38%
23.49%
31.47%
22.48%
100.00%
41.56%
37.05%
35.82%
35.46%
59.21%
37.69%
59.06%
31.82%
24.44%
60.84%
5.65%
21.88%
38.60%
19.90%
1288
DOW
4
47.91%
42.06%
31.61%
32.45%
31.92%
41.56%
100.00%
37.37%
38.56%
33.42%
52.35%
23.64%
40.98%
31.77%
27.14%
45.74%
4.57%
31.74%
26.81%
16.64%
1289
VZ
*
43.25%
37.38%
17.73%
29.19%
22.94%
37.05%
37.37%
100.00%
67.18%
35.89%
48.79%
26.11%
36.77%
28.74%
23.92%
44.98%
7.77%
19.28%
25.17%
17.64%
1290
T
5
41.47%
35.59%
21.37%
29.63%
24.46%
35.82%
38.56%
67.18%
100.00%
31.14%
47.72%
26.82%
39.62%
29.33%
23.90%
44.75%
3.59%
19.83%
25.44%
16.04%
1291
PFE
*
35.66%
32.86%
19.35%
26.88%
18.93%
35.46%
33.42%
35.89%
31.14%
100.00%
43.03%
22.24%
36.40%
24.43%
22.16%
42.01%
7.86%
17.74%
19.94%
18.09%
1292
AMP
*
65.32%
49.35%
32.06%
36.38%
31.62%
59.22%
52.35%
48.80%
47.73%
43.06%
100.00%
19.93%
58.80%
33.79%
29.32%
61.98%
7.90%
33.17%
39.84%
27.26%
1293
SAF
6
30.19%
23.59%
10.44%
17.79%
13.17%
37.69%
23.64%
26.11%
26.82%
22.24%
19.93%
100.00%
36.54%
14.71%
10.18%
34.40%
2.83%
11.42%
22.44%
8.86%
1294
CINF
*
55.26%
38.60%
22.31%
33.42%
18.60%
59.06%
40.98%
36.77%
39.62%
36.40%
58.77%
36.54%
100.00%
39.56%
20.62%
64.27%
9.14%
22.93%
42.52%
22.08%
1295
MDP
*
32.81%
34.41%
13.59%
29.25%
11.32%
31.82%
31.77%
28.74%
29.33%
24.43%
33.79%
14.71%
39.56%
100.00%
18.69%
37.57%
3.77%
15.86%
21.33%
16.15%
1296
EXPE
7
30.88%
26.57%
17.91%
16.45%
12.91%
24.44%
27.14%
23.92%
23.90%
22.16%
29.31%
10.18%
20.62%
18.69%
100.00%
23.58%
0.50%
13.45%
13.64%
14.43%
1297
TMK
*
56.97%
40.57%
21.07%
36.04%
22.16%
60.84%
45.74%
44.98%
44.75%
42.01%
61.94%
34.40%
64.27%
37.57%
23.58%
100.00%
9.00%
23.56%
41.26%
18.64%
1298
JAVA
*
11.14%
4.04%
2.01%
3.75%
3.41%
5.65%
4.57%
7.77%
3.59%
7.86%
7.90%
2.83%
9.14%
3.77%
0.50%
9.00%
100.00%
0.26%
7.86%
0.25%
1299
SUN
8
28.52%
20.94%
64.18%
17.95%
63.30%
21.88%
31.74%
19.28%
19.83%
17.74%
33.17%
11.42%
22.93%
15.86%
13.45%
23.56%
0.26%
100.00%
20.22%
14.65%
1300
AIZ
9
36.51%
29.40%
19.23%
19.82%
18.58%
38.60%
26.81%
25.17%
25.44%
19.94%
39.81%
22.44%
42.52%
21.33%
13.64%
41.26%
7.86%
20.22%
100.00%
15.05%
1301
KG
*
18.20%
17.68%
15.13%
16.34%
11.51%
19.90%
16.64%
17.64%
16.04%
18.09%
27.27%
8.86%
22.08%
16.15%
14.43%
18.64%
0.25%
14.65%
15.05%
100.00%
To this table
*
A
B
C
D
E
F
G
H
I
J
40
Risky assets
GS
DIS
NOC
DOW
T
SAF
EXPE
SUN
AIZ
41
GS
*
0%
0%
0%
0%
0%
0%
0%
0%
42
DIS
*
*
0%
0%
0%
0%
0%
0%
0%
43
NOC
*
*
*
0%
0%
0%
0%
0%
0%
44
DOW
*
*
*
*
0%
0%
0%
0%
0%
45
T
*
*
*
*
*
0%
0%
0%
0%
46
SAF
*
*
*
*
*
*
0%
0%
0%
47
EXPE
*
*
*
*
*
*
*
0%
0%
48
SUN
*
*
*
*
*
*
*
*
0%
49
AIZ
*
*
*
*
*
*
*
*
*
Excel tables to the web >> Excel Jeanie HTML 4
The question is, can this be done? It is to help me with a uni assignment to value stock portfolios.
Thanks
Paul
Hi
I don't know too much about standard deviation, but I was wondering if somebody could help me with a function to find 1 standard deviation (68%??) from the largest value (not the mean value) within a column of values. What i'm really after is the 'standard deviation boundaries' either side (i.e. 34% of values above the largest value and 34% below the lowest value) as a highlight.
im using excel mac 2008 so no vba im afraid.
here is an example of real data:
0
3,244
71,488
131,887
175,025
166,642
215,774
280,951
314,720
258,964
232,864
222,386
109,465
116,129
39,505
80,128
171,146
145,920
133,806
211,861
262,766
372,249
508,658
I have 13 separate worksheets with certain data; one worksheet for each month
of the year, and one to summarize the monthly result of the average, the
standard deviation, max and min of the data. For example, B25:M25 lists the
average, B26:M26 the standard deviation, B27:M27 the Max, and B28:M28 the
Min. How can I accurately summarize the monthly results from AVERAGE, STDEV,
MAX and MIN? Should I use the AVERAGE formulae or simply sum up the 12
figures and then divide them by 12?
Thank you for your help.
Orlando
In 3 rows (B1:B3) and in 5151 columns (till column GPD) I got alle the combinations which sum up to 1. (Thanx for the help in an other thread).
The first return I calculated in the following way.
=MMULT(TRANSPOSE(B1:B3);H12:H14)1
The Standard Deviation is calculated in this way.
=SQRT(MMULT(MMULT(TRANSPOSE(B1:B3);C24:E26);B1:B3))
See attached excel sheet.
It would be very helpful if a VBA script can be written which automatically calculates the 5151 returns and the 5151 standard deviation.
I'm trying to create a sequence of random returns that will always have a specific standard deviation.
I've tried "=NORMINV(RAND(),HypotheticalAVG,HypotheticalStDev)"
and it gives pretty decent results, but the Standard Deviation is generally between 0 to 5% off of the desired result.
Is it possible to do this?
Dear Excel Forumer,
I am not sure why my standard deviation formula return 6.22688E14 when i use formula =stdev(C2:C7). If, say for example, I change A7 to 0.1, it will return an absolute number of 0.040824829. That is my desired outcome. I wish to have col. A correspond to 0, while col. B correspond to 500.1, with stdev outcome an absolution value.
Please help. Thanks so much!!
1 A B C
2 0 500.1 500.1
3 0 500.1 500.1
4 0 500.1 500.1
5 0 500.1 500.1
6 0 500.1 500.1
7 0.1 500.1 500
8 Standard Deviation 0.040824829
Hello,
I currently have a graph of results with error bars showing standard deviation. However, i need to show 1 and 2 standard deviation. Is it possible to do this on the same graph say in a different colour or something or do i need to create 2 seperate graphs, one with one standard deviation and a 2nd with 2 standard deviation?
Thanks,
Andrew
This is my first post so I hope I make sense: I have a range of student raw test scores that will have a particular mean and standard deviation. How to I change the raw scores to match a different mean and standard deviation? Any suggestions would be appreciated, thanks.
Hello,
I am trying to calculate the standard deviation for each company, for a given year. I am using this formula but no success until now.
=STDEV(IF('WRDS data'!$M$2:$M$107352=A4&$B$3,'WRDS data'!$J$2:$J$107352))
M contains company codes and year let's say: @IBM1998 whereas J containes the values.
Any hint?
Thank you in advance.
How do you apply a standard deviation to a popluation of yes/no results?
I have a total of 69 yes and 85 no.
I understand that the standard deviation give an idea of how close the entire data set is to the average. But how does that work when you do not have actual numbers?
Any incite? thanks
So this forum has helped me in the past develop a standard deviation that removes max and min from the data set.
=STDEV(LARGE(E5:AB5,ROW(INDIRECT("2:"&COUNT(E5:AB5)1))))
Would if be possible to adjust this formula to ignore N/A's?
Hi guys I am having serious problems with a very large database I need to use. I am trying to calculate standard deviation but my results are being thrown by 0s in the data, is there a formula that ignores the 0 and still calculates the standard deviation, I also need to do this for mean formula aswell, any help would be greatly appreciated.

