# 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

Function syntax:

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

Function syntax:

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 74 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 signsUse 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 OscarYou can contact me through this contact form