Standard Deviation++ In Excel 


Standard Deviation++ In Excel  Excel 
View Answers 
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
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 Tutorials
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel. This means that y ...
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel. This means that y ...
NOW()  Display The Current Time in Excel
This Excel tip will show you how to display the current time within any Excel spreadsheet. This is a useful functio ...
This Excel tip will show you how to display the current time within any Excel spreadsheet. This is a useful functio ...
Helpful Excel Macros
Return the ISO Standards Start of the Year in Excel  First Monday of the Year  UDF
 Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
 Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Vlookup Macro to Return All Matching Results from a Sheet in Excel
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Return the ISO Week Number from a Date in Excel  UDF
 Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
 Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
WEEKNUM UDF for Excel Versions That Don't Contain This Function  UDF
 This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
 This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
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:
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)
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?
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
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
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.
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
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
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?
Any suggestions?
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
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
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 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
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 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
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.
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.
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
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.
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.
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?
=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
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
I am trying to learn VBA and started reading Wallenbach's book and I thought the best way to get up to speed fast was to work on some real problems.
So this is what I am trying to do.
I have a list of about 1000 stock price returns in the following format
jan 1 2008 .033
jan 2 2008 .01
jan 3 2008 .002
jan 4 2008 .0033
jan 5 2008 .04
What I want to do is calculate in VBA a moving 3 day standard deviation of returns. So I want the first calculation to include jan 1  jan 3 and place the answer in the cell to the right of jan 3 return. Then move down 1 row and calculate the standard deviation using jan 2  jan4 and place the answer in the cell to the right of jan 4 return...and so on until the last cell.
Thanks
So this is what I am trying to do.
I have a list of about 1000 stock price returns in the following format
jan 1 2008 .033
jan 2 2008 .01
jan 3 2008 .002
jan 4 2008 .0033
jan 5 2008 .04
What I want to do is calculate in VBA a moving 3 day standard deviation of returns. So I want the first calculation to include jan 1  jan 3 and place the answer in the cell to the right of jan 3 return. Then move down 1 row and calculate the standard deviation using jan 2  jan4 and place the answer in the cell to the right of jan 4 return...and so on until the last cell.
Thanks
I'm trying to create a standard deviation formula based on 52 values. Unfortunately though, excel only accepts 30. Here's my formula:
STDEV(S7,U7,W7,Y7,AA7,AC7,AE7,AG7,AI7,AK7,AM7,AO7,AQ7,AS7,AU7,AW7,AY7,BA7,BC7,BE7,BG7,BI7,BK7,BM7,BO 7,BQ7,BS7,BU7,BW7,BY7, CA7, CC7, CF7, CH7)
Excel then throws the "You've entered too many arguments for this function" error.
Does anyone know how I could set this up so I can use more than 30 values for this standard deviation?
Thanks.
STDEV(S7,U7,W7,Y7,AA7,AC7,AE7,AG7,AI7,AK7,AM7,AO7,AQ7,AS7,AU7,AW7,AY7,BA7,BC7,BE7,BG7,BI7,BK7,BM7,BO 7,BQ7,BS7,BU7,BW7,BY7, CA7, CC7, CF7, CH7)
Excel then throws the "You've entered too many arguments for this function" error.
Does anyone know how I could set this up so I can use more than 30 values for this standard deviation?
Thanks.