How to Use the AVEDEV Function in Google Sheets

Published on

I didn’t write any function related tutorials after introducing the FLATTEN. So, I think it’s high time to write one. In this tutorial let’s learn the use of the AVEDEV function in Google Sheets.

AVEDEV is a statistical function in Google Sheets. We can use this function to get the average of the magnitude of (absolute) deviations of a set of data from the mean of that set of data.

We can simply use this function with a given set of values. But that’s not enough to understand the mean deviation.

So, first, let’s see how to calculate the average deviation aka mean deviation without using the AVEDEV function in Google Sheets.

Calculating Mean Deviation in Google Sheets

In three simple steps, we can find the mean deviation of a set of values in Google Sheets. Here are those three steps.

Step # 1:

Let’s find the average deviation of the values 2, 6, 12, 14, 11, 8, 7, and 4.

The first step is to find the average of these 8 values. For that first sum the values (will get 64) and divide that sum by 8 (count of values).

So the average will be 8.

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

Step # 2:

Now let’s find the absolute deviation of the values (the absolute distance/magnitude of each value) from this mean. It’s like 8-2, 8-6, 8-12…

ValuesDistance of each values from the mean (8)
26
62
124
146
113
80
71
44

Step # 3:

Find the average of the values in column 2 which will be 3.25.

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

This way we can get the average deviation without using the AVEDEV function in Google Sheets.

If you use Google Sheets for the above manual calculation, it would be as below.

Manually Calculating Mean Deviation in Google Sheets

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

The AVEDEV spreadsheet function in Google Sheets makes the above mean deviation calculation hassle-free.

The function calculates the average, absolute distance from the average, and finally the average of the absolute distances/deviations, all in one go.

Syntax and Arguments

Syntax:

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

Arguments:

value1 – value1 is required.

value2 – optional.

You can use the values as comma-separated as per the syntax or as a reference to an array.

You can understand the same in the example section below.

Examples to AVEDEV Function in Google Sheets

Values Hard-Coded

In the first example, let’s use the above values in column A as comma-separated.

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

Here if you include the Boolean TRUE/FALSE values in the AVEDEV formula, it would be counted. Like in other spreadsheet functions, the value FALSE will be treated as 0 whereas the TRUE will be treated as 1.

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

Any text value, text within double quotes, cause the formula to return a #VALUE error as the text can’t be coerced to a number.

If you use a named rage with the numbers, the named range should be within curly brackets.

=AVEDEV(2,6,{app},14,11,8,7,4)

Values as an Array or Arrays

If you use an array, then the formula would be as below.

=avedev(A2:A9)

You can use multiple arrays to include scattered values in the average deviation calculation using the AVEDEV function in Google Sheets.

Here is that example.

Example to AVEDEV Function in Google Sheets

In the array use, the text values and blank cells will be ignored. So the formula won’t return any error.

That’s all about the AVEDEV function in Google Sheets.

Thanks for the stay. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.