Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Standard Deviation++ In Excel

Forum Register
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 5-year returns. For this data I need the standard deviation of the annualized results (that is, the 5-year 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

View Answers     

Similar Excel Tutorials

Error bars in Charts in Excel
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
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 ...
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 ...
Get a Date that is So Many Working Days Before or After a Date in Excel - WORKDAY
How to find a date that is so many days before or after a specified date, excluding weekends and any holidays. This ...

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
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
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
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
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

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.0699E-14

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

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

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.


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.


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


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


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.


I'm having trouble with my 12 month (or 1 year) return formula forcing it to return "na" if the company has less than 12 months of returns (as shown in column E of the attached file).

Also, do you have any suggestion how to improve my annualized formula in column F? So that I won't have to manually move the range for each individual company?

I'd love any suggestion or tip how to improve this.

Many thanks!

TN


I have 25,000 rows of data. Each row represents a referral. I have a start time and a stop time. I computed the elapsed time in a column using NETWORKDAYS, and that all looks good. When I try to compute the average time, I entered =average(M2:M25000) and it returns a value over 9 hours. (I doubt the averag was 9 hours, but might I digress). The issue here is that when I do the Standard Deviation, Excel is returning a number over 14.
Since this is TIME, I am assuming the the decimal refers to the part of the DAY . . .so .38 means over 8 hours, and the StDev of .615 represents 14 hours!!

How can my Standard Deviation exceed the Mean??
Do I need to handle Time differently when trying to extract statistical data?/

(25000 rows above this...)
0.204525463
0.615269346
0.38615559 Mean
0.615258899 StDev


Hi guys, I've been working on getting a standard deviation excel sheet together for use in a bell curve chart for finances. I'm just wondering if my worksheet is correct and need a doublecheck. I found a calculator online that figures out standard deviation from a list of numbers and mine came out identical.

However I'm a bit worried about the numbers I get. I'm guessing that when I'm calculating Stdev and get 13.43 this is a "daily" standard deviation. The monthly looks like a believable percentage for the security I'm looking at but when I calculate the weekly and yearly numbers those both look WAY too high. So I'm wondering if there's a problem with my formulas or is it that I'm not supplying it with enough "close" values to get viable data in those sections? Thanks for any help. I appreciate it

http://rapidshare.com/files/352723070/StandardDev.xls


Hi

I'm a beginner using Excel to do more advanced stuff than inserting chart and using simple formulas.

Now i need simulate data in 10 cells with a fixed standard deviation and a fixed mean

Example: Simulating the laptime for a runner where the mean should be 50 and the standard deviation 5,

I tried typing
=50+5*RAND()*(-1^(RANDBETWEEN(1;2)))
That gives me a mean close to 50 which is good but th standard deviation is no where near 5.
Anyone who knows how to solve this problem?

Thanks
Arvid