How to Use the AVEDEV Function in Google Sheets

AVEDEV is a statistical function in Google Sheets. You can use it to calculate the average of the absolute deviations of a data set from its mean.

In simpler terms, it gives you an idea of how spread out your data is—by measuring how far, on average, each value deviates from the mean.

You can simply use the AVEDEV function in Google Sheets with a given set of values. But to truly understand how it works, let’s first go through how to manually calculate mean deviation.

Table of Contents

Calculating Mean Deviation in Google Sheets

You can find the mean (or average) deviation of a set of values in Google Sheets in just three steps.

Step 1: Find the Mean

Let’s calculate the mean deviation of the values:
2, 6, 12, 14, 11, 8, 7, 4

First, find the average of these 8 values. To do that, sum the values and divide by the count (8).

=(2+6+12+14+11+8+7+4)/8

The result will be:

= 64 / 8 = 8

Step 2: Calculate Absolute Deviations

Now calculate the absolute deviation of each value from the mean (8). That means the absolute difference between each number and 8:

Values	Absolute Deviation from Mean (8)
2	6
6	2
12	4
14	6
11	3
8	0
7	1
4	4

Step 3: Find the Average of Deviations

Now average the deviations from Step 2:

=(6+2+4+6+3+0+1+4)/8

The result is:

Mean Deviation = 3.25

This is how you can manually calculate the average deviation in Google Sheets without using the AVEDEV function.

Manually Calculating Mean Deviation in Google Sheets

If you perform the above calculation directly in Google Sheets, you can create helper columns to compute the mean, the deviations, and finally the average deviation.

But instead of doing all that manually, let’s see how the AVEDEV function in Google Sheets makes it much simpler.

AVEDEV Function in Google Sheets: Syntax, Arguments, and Examples

The AVEDEV function in Google Sheets simplifies the entire process. It automatically calculates the mean, finds the absolute deviations from that mean, and then averages them—all in one step.

Syntax

AVEDEV(value1, [value2, ...])

Arguments

value1 – The first value or range. This is required.
value2 – Additional values or ranges (optional).

You can pass individual numbers, cell references, or even ranges. You’ll see examples below.

Examples of Using the AVEDEV Function in Google Sheets

Example 1: Hard-Coded Values

Let’s use the same numbers as earlier directly in the formula:

=AVEDEV(2,6,12,14,11,8,7,4)

This will return 3.25, just like our manual calculation.

Example 2: Including Boolean Values

Google Sheets treats FALSE as 0 and TRUE as 1 in numerical functions:

=AVEDEV(2,6,FALSE,14,11,8,7,4)

In this case, FALSE is counted as 0 in the calculation.

Example 3: Text Values

Text values will cause a #VALUE! error since they can’t be converted to numbers.

=AVEDEV(2,6,"text",14,11,8,7,4)  → #VALUE!

Example 4: Using Arrays or Ranges

You can also use a cell range to pass values:

=AVEDEV(A2:A9)

You can combine multiple ranges as well:

=AVEDEV(A2:A5, B2:B5)

Text and blank cells within ranges are ignored. Only numeric values are included in the calculation.

AVEDEV vs STDEV: What’s the Difference?

While both functions measure variability, AVEDEV returns the average absolute deviation, whereas STDEV (standard deviation) gives the square-root of the average of squared deviations. AVEDEV is simpler and less sensitive to outliers, making it useful for a general sense of spread. STDEV is more statistically rigorous and commonly used in formal analysis.

Conclusion

The AVEDEV function in Google Sheets is a powerful tool to quickly measure how much your data varies from the average. It eliminates the need for multiple helper columns and manual calculations.

Whether you’re working with hard-coded values, arrays, or cell ranges, using AVEDEV in Google Sheets will save you time and reduce the risk of errors.