How to use the STDEVA function
The STDEVA function estimates the standard deviation from a sample of values. It evaluates Boolean value TRUE as 1, FALSE and text values as 0 (zero).
Table of Contents
1. Introduction
What is Standard Deviation?
Standard deviation tells you how far from the average values are spread out. Both charts above have numbers and an average plotted, they share the exact same average however, the numbers are not the same.
Chart A above shows that the values are more spread out than the values in chart B. Chart A has a standard deviation of 23.45256334, standard deviation for chart B is 5.207075606. Standard deviation is fundamental in statistics.
A normal distribution is a symmetric, bell-shaped probability distribution that is commonly used in statistics and probability theory. The shape of the normal distribution is determined by its mean (μ) and standard deviation (σ). The mean represents the center of the distribution, while the standard deviation represents the spread or dispersion of the data around the mean.
- A normal distribution with a standard deviation of 0.5 is relatively narrow and tightly clustered around the mean.
The values in the distribution are concentrated within a smaller range, with most values falling closer to the mean. The curve appears tall and steep, indicating a higher concentration of data points near the mean. - A normal distribution with a standard deviation of 1 is the most commonly used normal distribution, often referred to as the standard normal distribution.
The standard deviation of 1 represents a moderate spread of the data around the mean. About 68% of the data falls within one standard deviation (±1σ) of the mean, and approximately 95% of the data falls within two standard deviations (±2σ) of the mean. The curve has a characteristic bell shape, with a smooth and gradual taper towards the tails. - A normal distribution with a standard deviation of 2 is relatively wide and spread out compared to the standard normal distribution. The data is dispersed over a larger range, with values more spread out from the mean. The curve appears shorter and flatter, indicating a lower concentration of data points near the mean.
When to use the STDEV.P function and the STDEV.S function?
Use STDEV.P if you have the full population data. Use STDEV.S if you have a sample of limited data from a larger population. STDEV.P will result in a lower standard deviation compared to STDEV.S on the same data.
Sample standard deviation is considered a better estimate for inferring population parameters.
What is inferring population parameters?
Population parameters refer to the actual values of statistics that describe an entire population, such as the population mean or standard deviation. However, the true population parameter values are often not known.
What is sample inferential statistics?
Sample inferential statistics are methods that allow using statistics calculated on a sample of data to infer the unknown population parameters.
For example:
- The sample mean can be used to estimate the population mean.
- The sample standard deviation can estimate the population standard deviation.
What is the difference between the STDEVA function and the STDEV.S function?
The STDEVA function evaluates:
- Boolean value TRUE as 1
- FALSE is 0 (zero).
- Text values are evaluated as 0 (zero) as well.
The STDEV.S function ignores Boolean and text values. estimates the standard deviation from a sample of values. Both formulas use the same math formula to calculate the standard deviation.
What is the difference between the STDEVA function and the STDEVPA function?
The STDEVA function estimates the standard deviation from a sample of values. If your data contains the entire population use instead the STDEVPA function to calculate the standard deviation.
Both functions evaluate:
- Boolean value TRUE as 1.
- FALSE is 0 (zero).
- Text values are evaluated as 0 (zero) as well.
STDEVPA math formula:
STDEVPA function = √(Σ(x - x̄)2/n)
STDEVA math formula:
STDEVA function = √(Σ(x - x̄)2/(n-1))
How can standard deviation be used to find outliers?
Standard deviation can be used to identify potential outliers in a dataset by defining a range based on the mean and standard deviation values. Observations that fall outside this range are considered outliers.
A common approach is to use the range μ ± 3σ, which covers approximately 99.7% of the data points if the distribution is normal.
Values below (μ - 3σ) are considered potential lower outliers.
Values above (μ + 3σ) are considered potential upper outliers.
μ - the mean
σ - standard deviation
What is the 68–95–99.7 rule?
The 68–95–99.7 rule, also known as the Empirical Rule, is a useful statistical principle that describes the percentage of data values that fall within certain standard deviations from the mean in a normal distribution.
- 68% of the data falls between μ ± 1σ
- 95% of the data falls between μ ± 2σ
- 99.7% of the data falls between μ ± 3σ
It's important to note that the 68–95–99.7 rule is an approximation and assumes that the data follows a normal distribution. In cases of skewed or heavy-tailed distributions the percentages may not hold true. Alternative methods may be needed to describe the data spread accurately.
2. STDEVA Function Syntax
STDEVA(number1,[number2],...)
3. STDEVA Function Arguments
| number1 | Required. This argument can be an array of constants or a cell reference to a group of values. |
| [number2] | Optional. Up to 254 additional arguments. |
4. STDEVA Function Example
A transportation analyst has collected data on the number of vehicles passing through a certain road section during specified time intervals. Assume the data follows a normal distribution. Find the outliers outside the 2 standard deviations (±2σ) range?
The data is:
| Traffic |
| 66 |
| 88 |
| 102 |
| 109 |
| 114 |
| 109 |
| A |
| 106 |
| 89 |
| 100 |
The argument is:
number1 = B16:B25
The data points are specified in cells B16:B25 in the image above.
The image above shows a chart containing a blue curve representing the probability mass function of a normal distribution where the mean is 98 and the standard deviation is 34.08, calculated below. The chart shows the standard deviations denoted ±1σ, ±2σ, and ±3σ, cells E19:E20 display ±2σ standard deviation. 2σ = 29.96 and -2σ = 166.26
Formula in cell E15:
Cell E15 displays 34.08 which represents the standard deviation (σ) based on the values in B15:B25. We can use this value and the arithmetic mean to calculate the ±2σ standard deviations.
Formula in cell E19:
The formula in cell E19 first calculates the arithmetic mean using the AVERAGE function. It then takes this mean value and subtracts twice the standard deviation from it. Specifically, it subtracts the product of 34.08 and 2 from 99, resulting in a value of 70.25.
98.11 - (34.08 × 2) = 98.1111111111111 - 68.1504870774149 = 29.96
Formula in cell E20:
The formula in cell E20 uses the AVERAGE function to calculate the arithmetic mean then calculates the total with the standard deviation multiplied by 2.
98.11 + (34.08 × 2) = 98.1111111111111 + 68.1504870774149 = 166.26
The chart in the image above shows a normal distribution with the standard deviations denoted ±1σ, ±2σ, and ±3σ. One data point is outside the ±2σ standard deviations which is 0 (zero) representing the text string "A" in cell B22.
5. How is the STDEVA function calculated?
STDEVA function = √(Σ(x - x̄)2/(n-1))
x̄ - arithmetic mean
n - count of values
To calculate the standard deviation based on a sample using only three numbers follow these steps:
- Find the mean (average) of the three numbers.
- Let's say the three numbers are x1, x2, and x3.
- The mean (x̄) is calculated as: x̄ = (x1 + x2 + x3) / 3 - Calculate the deviation of each number from the mean.
- Deviation of x1 from the mean: x1 - x̄
- Deviation of x2 from the mean: x2 - x̄
- Deviation of x3 from the mean: x3 - x̄ - Square each of the deviations.
(x1 - x̄)2
(x2 - x̄)2
(x3 - x̄)2 - Find the sum of the squared deviations.
- Sum of squared deviations = (x1 - x̄)2 + (x2 - x̄)2 + (x3 - x̄)2 - Divide the sum of squared deviations by (n - 1), where n is the number of data points (in this case, n = 3).
- Variance = Sum of squared deviations / (n - 1)
- Variance = Sum of squared deviations / 2 - Take the square root of the variance to obtain the standard deviation.
- Standard deviation = √(Variance)
Functions in 'Statistical' category
The STDEVA function function is one of 73 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form