By Paige Bennett

If you’ve ever taken a statistics class, the words ‘standard deviation’ might intimidate you. This complex formula provides insightful information for datasets that averages alone cannot reveal, and thankfully, Excel makes calculating this statistic easier than putting pencil to paper.

Standard deviation is frequently used by financial professionals as it can help determine risks in stock portfolios and is often applied to return on investment (ROI).

For marketing professionals, standard deviation can reveal variabilities and risks in datasets that will ultimately help guide campaign decisions.

What is standard deviation?

Simply put, standard deviation is a mathematical term that measures the variation in a set of values. In marketing, standard deviation can help account for widely varying costs or sales. If the numbers in a data set are spread far apart, they have a higher standard deviation.

This measurement can help assess risk when deciding how much budget can be allocated toward certain campaigns based on the standard deviation of the ROI, just to name one example.

How to Calculate Standard Deviation in Excel

There are six standard deviation formulas in Excel, which will be used based on whether you need to calculate sample standard deviation or population standard deviation. This is easy to identify in Excel, as the three formulas for population standard deviation include a P (.P, PA, or P at the end of STDEV).

1. STDEV.S
2. STDEVA
3. STDEV
4. STDEV.P, STDEVPA, STDEVP

STDEV.S

If you are dealing with sample standard deviation and do not need to account for text or logical values, STDEV.S is the formula you will use to calculate standard deviation in Excel.

STDEVA

Alternatively, if you do need to account for text and logical values, use STDEVA, which will consider text and FALSE logical values to 0, while TRUE logical values will be read as 1.

STDEV

STDEV is simply the sample standard deviation formula that will work with older forms of Excel (2007 and before). It is the same as STDEV.S.

STDEVP, STDEVPA, STDEVP

You will almost exclusively use STDEV.S, STDEVA, or STDEV. When determining population standard deviation, you would have to include all datasets for the entirety of the population.

This can actually be far more data and much less useful than examining a smaller portion of the data, or a sample, in which case you would use one of the sample standard deviation formulas.

If you did, for some reason, need to determine the population standard deviation, you could still retrieve sample standard deviations and apply them to the larger dataset. The likelihood of needing these population standard deviation formulas is slim.

As stated by Microsoft, “[STDEVP] has been replaced with one or more new functions that may provide improved accuracy and with names that better reflect their usage. Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel.”

<h2 style="font-weight: …read more

Source:: HubSpot Blog

Author: Aaron