Excel goes far beyond just rows and columns. It can be a great platform to store your data and keep things organized. The platform also helps you take a break from tedious manual work and apply built-in formulas to make work easy and fast. And, if you are still struggling with calculating a standard deviation of the data you collected, we are here to do the math for you.
Wait, but what is the standard deviation?
Read the blog to explore what it is, the formula and some examples.
What does Standard Deviation mean?
This technique is a statistical measure that allows you to calculate the dataset’s dispersion relative to the mean. In short, it is calculated as the square root of the spread of numbers in your datasheet.
By using this technique, you can calculate the variation between different data points. It can also be used to compare the variation among the data points with the same standard but a diverse data range.
Let’s all this understand with an example.
Here are two data sets.
- 15, 15, 15, 14, 16
- 2, 7, 14, 22, 30
These data sets may look the same, but the last one is more evenly spread.
Technically speaking, if the data points are situated further from the mean, the deviation within that set will be higher. In other words, the more space between the data points, the greater the deviation.
This technique is usually used in financing, where it is often applied to the annual return rate of an investment. A higher deviation would mean a more significant price range. Volatile stocks usually have a higher variation, while a company with a good reputation has a lower standard deviation.
Calculating Standard Deviation In Excel
Because standard deviation is used to measure market volatility, it is often used to strategize investments and trading. Several portfolio managers and traders use this technique to calculate the degree of risk involved in a particular investment.
Now, you might think it’s tricky to calculate. But excel can help you with that. Here’s how to calculate deviation using excel.
To get started, there are six formulas you can use to determine the standard deviation.
To calculate sample deviation, you can use formulas like STDEV.S, STDEV and STDEVA. However, if you need to calculate the deviation for an entire population, you can use formulas like STDEV.P, STDEVP and STDEVPA.
In this case, the term population implies that you are considering entire datasets present in your data. If using the total population is impossible, you can use sample formulas. Technically, you can use the sample formula to calculate a sample deviation and then infer it from the entire population.
Here’s an explanation of the three sample standard deviation formulas:
- STDEV.S: You can use the formula if your data set is numeric, as it will ignore all logical values.
- STDEVA: This formula is used when the data set includes both numeric values and logical values. In the case of logic, “False” is taken as 0 and “True” is taken as 1.
- STDEV: Compatible with older versions of Excel, this formula does the job same as STDEV.S, but on Excel, that is, 2007 or prior.
Using STDEV.S In Excel
Again, STDEV.S only considers numerical values and disregards textual and logical values.
Excel’s STDEV.S function has the following syntax: STDEV.S(number1,[number 2],…
The first argument in the formula must be the number 1. The first number represents the sample’s initial element. In this case, you may use a range name, an array, or a display reference instead of arguments separated by commas.
Formula number 2 is an optional argument. It can refer to an array, a named range, a data point, or an array reference. It is possible to use up to 254 additional arguments.
Example Of STDEV.S
Consider a dataset with a variety of weights taken from a population sample. The formula, when used with the numbers in column A, will result in the following: =STDEV.S(A2:A10).
Excel will then deliver the average and standard deviation of the applied data. The majority of the group would fall within the weight range of 150-2 or 150+2 if the average is 150 and the standard deviation is 2.
When a formula is an input, Excel’s Formula bar will also display it. The equal sign must always be used when entering a formula.
Let Us Do The Maths for You
Basic Statistics + Comparative Analysis = Tangible Solutions
Seeing a difference between two numbers is easy, but determining whether that difference is statistically significant takes a little more effort. Especially if your question has several possible answers or you’re comparing findings from different groups of respondents, the process can be tricky.
Don’t refrain from investing in the proper technologies to alleviate the burden of manual analysis. Take the hassle out of the equation and let SurveyPoint do all the heavy lifting for you.
Heena Shah – Content Writer at Sambodhi
