Standard Deviation++ In Excel

'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

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

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?

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

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.

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.

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.

Hi there,

I have a problem with obtaining results from an equation with two input variables. Now, this isn't easy to explain so bear with me.

In column A I have randomly generated number from a normal distribution with a mean and standard deviation given by the user. I have the same in column B but with different mean and standard deviation. Now, the mean and standard deviation for each of column A and B are actually calculated from 2 variables called amplitude and position. In column C I have the ratio of the A/B. My equation takes the standard deviation of Column C giving the result.

What I want to do is give a range of values for the amplitude and position, and return the calculated standard deviation of the resulting column C.
Thus far I have been entering the 2 values manually and getting the answer, but have over 300 combinations to enter.

Can anyone help?

Thanks

Hi

I want to analyse 2 periods of traffic volumes (in excel). I am hoping to confirm the best methods.

At present, I have taken the variance from the previous year comparable period (=SQRT((year 2 February volume - year 1 February volume)^2) for each of the 12 periods in the year. I have then taken the mean - this gives me the average movement on last year, accounting for seasonality.

I have then calculated the standard deviation of this mean population, highlighting data points that fall outside of the standard dev

Should I have added the last year period to current year period then taken the average of those amounts, to get the mean to use for calculating the standard dev

Not sure on the most statistically 'sound' method.