AVEDEV function

The AVEDEV function in Excel calculates the average of the absolute deviations of data points from their mean. It is used to measure the variability or dispersion of a dataset, showing how far the data points are from the average value.

Syntax

=AVEDEV(number1, [number2], ...)

• number1, number2, …: These are the numbers or ranges for which you want to calculate the average deviation. You can input up to 255 numbers or ranges.

Description

• AVEDEV returns the average of the absolute deviations of data points from their arithmetic mean.
• It helps to understand how spread out the data points are around the mean, but unlike standard deviation, it doesn’t square the deviations.

The formula for the average deviation is:

$$\frac{1}{n} \sum_{i=1}^{n} |x_i – \bar{x}|$$

Where:

• $x_i$ = each individual data point,
• $\bar{x}$ = the arithmetic mean of the data points,
• $n$ = the number of data points.

Example 1: Basic Usage

Given the numbers 4, 8, 6, 5, 3, to calculate the average deviation:

=AVEDEV(4, 8, 6, 5, 3)

Result:

The result is 1.6, which means that, on average, each value deviates from the mean by 1.6 units.

Example 2: Using Cell Ranges

If the numbers 4, 8, 6, 5, 3 are in cells A1:A5, you can use:

=AVEDEV(A1:A5)

Result:

The result is 1.6, as calculated above.

Example 3: Large Dataset

If you have a large dataset and want to calculate the average deviation for the range B1:B100, you can use:

=AVEDEV(B1:B100)

Result:

The function will calculate the average deviation for the numbers in the specified range.

Usage Notes

• AVEDEV ignores empty cells and cells with text or logical values.
• It differs from STDEV and VAR functions because it uses absolute deviations instead of squared deviations, making it less sensitive to outliers.

Practical Application

• AVEDEV is useful in situations where you want to know the average amount of variation in a dataset but are less concerned about large outliers, as it treats all deviations equally by taking the absolute value.