# Google Sheets AVEDEV Function: Syntax, Examples & Tips

- URL: https://betteranalyst.com/google-sheets/avedev-function
- Author: Better Analyst

Formula Bot is now Better Analyst—same account, more powerful analysis.Formula Bot is now Better Analyst—same account and workflows, with more powerful analysis, dashboards, and automation.

[Learn what changed](https://betteranalyst.com/formula-bot-is-now-better-analyst)

Returns the average of the absolute deviations of values from their mean.

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

Generate FormulaExplain Formula

One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward.

Describe the AVEDEV you need in plain EnglishGenerate Formula

## Parameters

| Parameter | Required | Description |
| - | - | - |
| **value1** | Yes | The first value or range in the data set. |
| **value2, ...** | No | Additional values or ranges to include. |

## 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](https://betteranalyst.com/google-sheets/average-function), 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

| Feature | AVEDEV | STDEV |
| - | - | - |
| Measures | Average absolute distance from the mean | Square root of the average squared distance |
| Effect of outliers | Moderate | Stronger, because deviations are squared |
| Interpretation | "Typical distance from average" | Used in most statistical tests and formulas |
| Units | Same as the data | Same as the data |

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](https://betteranalyst.com/google-sheets/count-function) 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](https://betteranalyst.com/google-sheets/round-function) for dashboards.
- To measure spread only for certain rows, filter them first: `=AVEDEV(FILTER(B2:B100, A2:A100="West"))`.

## Related Functions

- [AVERAGE](https://betteranalyst.com/google-sheets/average-function): the centre AVEDEV measures from
- [AVERAGEIF](https://betteranalyst.com/google-sheets/averageif-function): mean of a subset
- [COUNT](https://betteranalyst.com/google-sheets/count-function): check how many numbers are included
- [ROUND](https://betteranalyst.com/google-sheets/round-function): tidy up the result

## Example use case

A warehouse manager tracks daily order processing times. =AVEDEV(B2:B31) shows that on a typical day processing time is about 12 minutes away from the monthly average, a simple measure of consistency to share with the team.

## Need more help with Google Sheets formulas?

Sign up for Better Analyst and get unlimited AI-powered formula generation, explanations, and advanced data analysis.

[Create Free Account](https://betteranalyst.com/signup)[Try Formula Generator](https://betteranalyst.com/excel-ai)

## Google Sheets how-to guides

- [How To Subtract In Google Sheets](https://betteranalyst.com/google-sheets/how-to-subtract-in-google-sheets)
- [How To Transpose Data In Google Sheets](https://betteranalyst.com/google-sheets/how-to-transpose-data-in-google-sheets)
- [How To Unhide Columns In Google Sheets](https://betteranalyst.com/google-sheets/how-to-unhide-all-columns-in-google-sheets)
- [How To Unhide Rows In Google Sheets](https://betteranalyst.com/google-sheets/how-to-unhide-all-rows-in-google-sheets)
- [How To Unprotect Google Sheets](https://betteranalyst.com/google-sheets/how-to-unprotect-google-sheets)
- [How To Wrap Text In Google Sheets](https://betteranalyst.com/google-sheets/how-to-wrap-text-in-google-sheets)
- [Google Sheets Conditional Formatting](https://betteranalyst.com/google-sheets/google-sheets-conditional-formatting)
- [How To Add A Column In Google Sheets](https://betteranalyst.com/google-sheets/how-to-add-a-column-in-google-sheets)

## Explore all Google Sheets formulas

Browse our complete library of Google Sheets formula guides

Compare your options

## Decide with evidence, not a sales pitch.

Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose.

1. [01ChatGPTOpen a balanced comparison prompt in ChatGPT.](https://chatgpt.com/?prompt=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.)
2. [02ClaudeOpen the same research prompt in Claude.](https://claude.ai/new?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.)
3. [03PerplexityResearch the comparison with linked web sources.](https://www.perplexity.ai/search?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.)

AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.