Simple way to Calculator Volatility?

Do you have a preferred method for calculating volatility? According to this page at investopedia (http://www.investopedia.com/articles...volatility.asp ), volatility is typically computed as the standard deviation of returns (in Excel, this would be one of the STDEV...() functions: https://support.office.com/en-us/art...90033e188#bm19 ). This wikihow page describes one way to do this in Excel: http://www.wikihow.com/Calculate-His...sing_Excel_sub

However, the investopedia article also suggests that, while standard deviation is an easy way to compute volatility, it may not be the best measure of volatility. It would be up to you to decide what your desired approach to computing volatility is. Once you decide how you want to compute volatility, describe that process/algorithm to us and we can help you program that into the spreadsheet.

I am not in financials or investing, so I don't know (other than searching websites like above) how investors measure volatility. So I am also not sure about a 0 to 10 type measure for volatility.

The standard deviation will return 0 if all values in the range are the same, and progressively larger numbers the more variability there is in the data. If the standard deviation is not what you are looking for, I would suggest that you consult someone more knowledgeable in investments (I don't know if we have any users here who are investors) who knows how to measure volatility. Once you know how volatility is measured, then you can bring that information back here and we can help you program that into Excel.

As MrShorty notes, it isn't clear exactly what sort of measurement you're seeking. Standard deviation measures volatility in relation to the mean, as MrShorty pointed out, so that figure would give you a measure how much the data typically deviates from the horizontal line representing the mean. Another alternative would be to use the R-Squared value, which would measure the volatility in relation to the 'best fit' linear trendline, so instead of comparing your data to the mean, you'd be comparing it to the closest linear match to your data set. You can view the R-Squared value by right-clicking on the data points on your chart, selecting "Add Trendline", then checking the box for "Display R-Squared Value". The closer the R-Squared value is to one, the better the data fits the trend line displayed on the map.

In my attachment, I've displayed the trend lines and R-Squared values on your charts and also included a basic STDEV formula to show the returns using that measurement. Hopefully one of these features will suit your needs?

	L	M	N
1	1.08E-05	1.17614	L1:M5: {=LINEST(A1:A30, , , TRUE)}
2	2.47E-06	4.39E-05
3	0.4035	0.000117
4	18.9397	28
5	2.6E-07	3.85E-07

The highlighted value is the same R2 value that appears on the chart.

It is as shown: select L1:M5, paste =LINEST(A1:A30, , , TRUE) in the formula bar, press and hold the Ctrl and Shift keys, then press Enter.

If you have your linear fit values calculated, r^2 is easily calculated using the RSQ() function: https://support.office.com/en-us/art...d-a8364f2be08f There are other, related functions as well, such as CORREL() and PEARSON(). The LINEST() function can also output r^2 if you instruct the function to output the additional statistics (TRUE for 4th argument) https://support.office.com/en-us/art...a-fa7abf772b6d You can nest the LINEST() function inside of an INDEX() function, if you want to only return the r^2 value without the other data [=INDEX(LINEST(kown_ys,known_xs,TRUE,TRUE),3,1)]. r^2 will be one if the regression is a perfect fit (interpreted as "no volatility"?) and 0 if there is no fit at all (interpreted as "high volatility"?).

These functions all fit within the statistical category. It might be valuable to look at all of the functions in the statistical category so you can see what functions Excel has built in for these kind of statistical calculations: https://support.office.com/en-us/art...rs=en-US&ad=US