If you’ve taken a statistics course before, the words “standard deviation” might intimidate you. This complex formula provides insightful information for records that averages alone cannot reveal, and luckily, Excel makes calculating these statistics easier than putting a pencil on paper.
The standard deviation is widely used by financial professionals as it can help determine risk in stock portfolios and is often applied to return on investment (ROI).
For marketers, the standard deviation can reveal variabilities and risks in data sets that ultimately aid campaign decisions.
What is standard deviation?
In simple terms, standard deviation is a mathematical term that measures the variation in a series of values. In marketing, the standard deviation can help to take widely varying costs or sales into account. If the numbers in a data set are far apart, they have a higher standard deviation.
This measurement can help assess risk when deciding how much budget to allocate to specific campaigns based on the standard deviation of ROI, to name just one example.
How to calculate the standard deviation in Excel
There are six standard deviation formulas in Excel that are used based on whether you need to calculate the sample standard deviation or the population standard deviation. This is easy to see in Excel because the three population standard deviation formulas contain a P (.P, PA, or P at the end of STDEV).
- STDEV.S.
- STDEV
- STDEV
- STDEV.P, STDEVPA, STDEV.
STDEV.S.
When you are dealing with sample standard deviation and don’t need to consider text or logical values, STDEV.S is the formula you use to calculate standard deviation in Excel.
STDEV
Alternatively, if you need to consider text and logical values, use STDEVA, which treats text and logical FALSE values as 0 while reading logical TRUE values as 1.
STDEV
STDEV is simply the example standard deviation formula that works with older Excel forms (2007 and earlier). It’s the same as STDEV.S.
STDEV., STDEVPA, STDEV.
You will almost exclusively use STDEV.S, STDEVA, or STDEV. In determining the population standard deviation, you would need to include all of the data sets for the entirety of the population.
In fact, this can be a lot more data and a lot less useful than examining a smaller fraction of the data or a sample. In this case, you would use one of the sample standard deviation formulas.
If for some reason you need to determine the population standard deviation, you can still get sample standard deviations and apply them to the larger data set. The likelihood of using these formulas for the standard deviation of the population is small.
As stated by Microsoft, “[STDEVP] has been replaced with one or more new features that may provide improved accuracy and names that better reflect their use. Although this function is still available for reasons of backward compatibility, you should use the new functions immediately as this function may not be available in future versions of Excel. “
Example of a standard deviation in Excel
STDEV.S, STDEV, or STDEV are the most common formulas for marketers who want to calculate the standard deviation of samples in Excel.
These formulas specifically calculate the standard deviation for a sample of a data set, so the result determines the variability from the mean (mean) of the data.
Next we will determine how to find and use formulas for standard deviation in an Excel spreadsheet.
Excel formula for standard deviations
As mentioned above, there are a total of six standard deviation Excel formulas, but you only need one or two of which (depending on the version of Excel you are using).
The syntax of the standard deviation formula in Excel for STDEV.S is
STDEV.S (number1,[number2], …)
number 1 is required and refers to “The first number argument that corresponds to a sample of a population. You can also use a single array or a reference to an array instead of comma separated arguments, ”as Microsoft explains.
Number 2, … it’s optional. You can include up to 254 number arguments relating to sample sets or population data, or you can use an array or a reference to an array in place of number arguments.
This is the STDEV.S formula used by Excel:
x is the sample mean value AVERAGE (number1, number2,…) and No is the sample size. Fortunately, Excel makes it easy to apply this formula to data to calculate the standard deviation.
Image via Paige Bennett
Excel standard deviation function
How do you find the Excel standard deviation function you need? In Excel, navigate to the “Formulas” header in the top navigation bar. For versions of Excel after 2007, the second navigation bar offers a list of common formulas and at the end “Additional functions”. Click on “More Functions” then “Statistical” and finally scroll down to STDEV.S or any other standard deviation formula you may need. You can then enter Number1 and Number2,….
Those using an older version of Excel don’t have the STDEV.S formula, but they can navigate to STDEV in a similar way.
Why Standard Deviation Matters to Marketers
Maybe this seems easy to some marketers, or maybe this is a lot of statistical statements that sound complex. Either way, mastering this formula and Excel function is vital for marketers.
It is common in marketing to discuss averages for records, and this can be instructive. But it can also leave out some important information that could affect a campaign.
The standard deviation can indicate risk, volatility, or variability in a data set. An average can represent a promising campaign that can be allocated money, but the standard deviation can indicate the potential risk and benefit of a campaign. Together, averages and standard deviations can provide a comprehensive view of a data set so that marketers can make the best decisions based on all of the information available.
For example, let’s say we have two companies with different order averages.
Company one has an average order volume of $ 1,000. Company two has an average order volume of $ 1,500.
But company two has a standard deviation of $ 500 while company one has a standard deviation of $ 50. Company two has a higher order average on the surface, but a deeper dive shows it is riskier.
Standard deviation can help marketers assess risk
Averages can be useful in showing marketers promising campaigns and predicting their outcomes. What averages do not show, however, is the risk and variability within the data.
A higher average number of sales may not have much variability and ultimately no risk. While the standard deviation has long been used in finance to assess stock portfolio risk and help professionals determine where to invest or what to expect for ROI, marketers can also benefit from this statistical analysis in their own work.
You might just find some profitable surprises and avoid risky, volatile campaigns or companies by using both averages and standard deviations in your decisions.