How to use the AVEDEV function
What is the AVEDEV function?
The AVEDEV function calculates the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set. Variability is the extent to which a distribution is stretched or squeezed. The absolute deviation from the mean helps us understand how "spread out" the values in a data set are, it describes the variation in a data set.
Table of Contents
1. Introduction
What is an average?
An average is a statistical measure of the central or typical value in a data set that provides an estimation of expected results by calculating the sum of values divided by total occurrences. Common averages include the arithmetic mean, median, and mode, each of which summarize data in different ways.
What is an absolute deviation?
Absolute deviation is a statistical measure of spread or variability in a data set that sums the total distance each data point is from a measure of central tendency such as the mean or median without regard to direction plus or minus. It provides an easy way to quantify dispersion from a central value unlike variance.
What is mean?
The arithmetic mean, more simply referred to as the "average" or mean, is a fundamental concept in statistics that is computed by summing all values in a sample or population and dividing this total by the number of values, giving the balance point if the data were evenly distributed.
What is variability?
Variability refers to a characteristic of a data set describing the amount of dispersion or spread between data points that measures how far individual numbers tend to deviate from the mean, reflecting the breadth of the distribution; common ways to quantify variability include the variance, standard deviation, and interquartile range.
What is a distribution?
A distribution in statistics is the description of the relative number of times each different outcome will occur in a sample or population, modeled by a histogram or probability distribution that reflects central tendency, variability, skew, normality, and other aspects of the data spread.
What does it mean if a distribution is stretched or squeezed?
If a distribution appears stretched, it indicates higher variability with values more dispersed widely around the mean, while a squeezed distribution has lower variability with values clustered closer together near the mean due to a smaller standard deviation.
What is variation?
Variation, or variability, is the amount of diversity present in a sample or population revealed by the tendency of individual data points to differ from the measure of central tendency, with more variation meaning a wider dispersal and larger differences among values.
2. AVEDEV Function Syntax
AVEDEV(number1, [number2], ...)
3. AVEDEV Function Arguments
| number1 | Required. A cell reference to number(s) or an array of numbers. |
| [number2] | Optional. Up to 254 additional number arguments. |
4. AVEDEV Function Example 1
This example demonstrates how to use the AVEDEV function, the image above shows the following values in cell range B3:B10 : 8, 4, 5, 2, 5, 14, 9, and 6
The formula in cell D3 calculates the average (mean) deviation based on the numbers described above. The AVEDEV returns always an absolute number meaning the result is always a positive number.
The average deviation is a measure that describes how spread out the numbers are from the average.
Formula in cell D3:
Here is how the average deviation is calculated.
- Calculate the average.
- Distance from the average.
- Add deviations
- Divide the total by the number of observations
Calculate the average
The average is calculated like this: 8+4+5+2+5+14+9+6 = 53
53 / 8 = 6.625
Absolute distance from the average
| 8-6.625 | = 1.375
| 4-6.625 | = 2.625
| 5-6.625 | = 1.625
| 2-6.625 | = 4.625
| 5-6.625 | = 1.625
| 14-6.625 | = 7.375
| 9-6.625 | = 2.375
| 6-6.625 | = 0.625
Add the deviations
1.375 + 2.625 + 1.625 + 4.625 + 1.625 + 7.375 + 2.375 + 0.625 = 22.25
Divide the total by the number of observations
The total number is 8.
22.25 / 8 = 2.78125
2.78125 matches the calculated value in cell D3.
The chart above shows a thicker black line which represents the average 6.625, the dashed lines displays the average deviation meaning the distance from the average. The first dashed line shows the upper average deviation:
6.625+2.78125 = 9.40625
The second dashed line shows the lower average deviation.
6.625-2.78125 = 3.84375
5. AVEDEV Function Example 2
This second example shows how to calculate the average deviation based on a frequency table. The AVEDEV function can't calculate the average deviation based on a frequency table, we need to create a new formula that handles frequency tables.
The image above shows the following frequency table in cell range B3:C10:
| Frequency | Number |
| 2 | 8 |
| 4 | 4 |
| 1 | 5 |
| 3 | 2 |
| 6 | 5 |
| 2 | 14 |
| 3 | 9 |
| 5 | 6 |
The following formula calculates the average deviation by:
- Calculate the average based on the numbers and frequency in the frequency table.
- Calculate the distance from the average.
- Multiply the distance by the frequency.
- Add deviations.
- Divide the total by the number of observations.
Formula in cell E3:
Excel 365 subscribers may use this smaller formula:
The major advantage is that you only need to adjust two cell references compared to the larger formula above.
Here are the values based on the frequency table:
| 8 | 5 | 5 | 9 | 6 |
| 8 | 2 | 5 | 9 | 6 |
| 4 | 2 | 5 | 9 | |
| 4 | 2 | 5 | 6 | |
| 4 | 5 | 14 | 6 | |
| 4 | 5 | 14 | 6 |
The AVEDEV function returns the same value as our formula above, the calculations are correct.
Explaining the formula in cell E3
Step 1 - Calculate the average based on the frequency table
The SUM function allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.
Function syntax: SUM(number1, [number2], ...)
SUM(C3:C10*B3:B10)/SUM(B3:B10)
becomes
SUM({8;4;5;2;5;14;9;6}*{2;4;1;3;6;2;3;5})/SUM({2;4;1;3;6;2;3;5})
becomes 158/26 and returns 6.07692307692308
Step 2 - Calculate the distance
The ABS function converts negative numbers to positive numbers.
Function syntax: ABS(number)
ABS((C3:C10-SUM(C3:C10*B3:B10)/SUM(B3:B10)))
becomes
ABS(({8;4;5;2;5;14;9;6}-6.07692307692308)
and returns {1.92307692307692; ... ;0.0769230769230802}
Step 3 - Multiply the distance by the frequency
ABS((C3:C10-SUM(C3:C10*B3:B10)/SUM(B3:B10)))*B3:B10
returns {3.84615384615385; ... ;0.384615384615383}
Step 4 - Add values and return total
SUM(ABS((C3:C10-SUM(C3:C10*B3:B10)/SUM(B3:B10)))*B3:B10)
returns 56.9230769230769
Step 5 - Divide total by the number of observations
SUM(ABS((C3:C10-SUM(C3:C10*B3:B10)/SUM(B3:B10)))*B3:B10)/SUM(B3:B10)
becomes
56.9230769230769 / 26 equals 2.18934911242604 This value matches the value in cell E3.
Functions in 'Statistical' category
The AVEDEV 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