Google Sheets AVEDEV Function: Syntax, Examples & Tips

What Is AVEDEV?

AVEDEV measures how spread out a set of numbers is. It returns the mean absolute deviation: the average distance between each value and the mean of the data. A small result means the values sit close together; a large result means they are scattered.

It is easier to interpret than standard deviation because it is expressed in the same units as the data and is simply an average distance. If AVEDEV of delivery times is 1.5 days, a typical delivery is about a day and a half away from the average.

How AVEDEV Works

AVEDEV follows three steps:

1. Calculate the mean of all numeric values.
2. Find the absolute difference between each value and that mean.
3. Average those absolute differences.

Like AVERAGE, it ignores text and empty cells in ranges. Zeros are included.

Worked Example

Take the values 2, 4, 6, and 8.

• Mean = 5
• Absolute deviations: |2−5| = 3, |4−5| = 1, |6−5| = 1, |8−5| = 3
• Average deviation = (3 + 1 + 1 + 3) ÷ 4 = 2

=AVEDEV(2, 4, 6, 8)

Returns 2.

Basic Examples

Spread of a column

=AVEDEV(B2:B31)

Returns the mean absolute deviation of the values in B2:B31.

Compare consistency between two teams

=AVEDEV(B2:B50)
=AVEDEV(C2:C50)

The team with the smaller result performed more consistently, even if both have similar averages.

Relative spread

=AVEDEV(B2:B31) / AVERAGE(B2:B31)

Expresses the spread as a fraction of the mean, which helps when comparing data sets on different scales.

AVEDEV vs. Standard Deviation

For the same data, AVEDEV is never larger than the population standard deviation. Use AVEDEV for plain-language reporting on variability, and STDEV when you feed the result into further statistics such as confidence intervals.

Common Use Cases

• Quality control: how far product measurements typically stray from target
• Forecast accuracy: spread of forecast errors
• Operations: consistency of delivery or response times
• Finance: typical variation of daily returns
• Education: how spread out a class's scores are

Common Errors

• #NUM! or #DIV/0!: The input contains no numbers, often because values were imported as text.
• Numbers stored as text: These are ignored silently. Check with COUNT that the expected number of values is being used.
• Errors in the range: Any error value in the referenced cells is returned by AVEDEV.
• Mixing units: Combining ranges measured in different units gives a meaningless result.

Tips and Best Practices

• AVEDEV uses the mean as its centre. If your data is heavily skewed, also look at the median.
• Present AVEDEV alongside the mean, for example "average 42 minutes, typically ±6".
• Round the result with ROUND for dashboards.
• To measure spread only for certain rows, filter them first: =AVEDEV(FILTER(B2:B100, A2:A100="West")).

Related Functions

• AVERAGE: the centre AVEDEV measures from
• AVERAGEIF: mean of a subset
• COUNT: check how many numbers are included
• ROUND: tidy up the result