In the previous blog. we implemented the **STDEV.P Function **to calculate the standard deviation. The AVEDEV function is also one of the important statistical functions. The usage of the AVEDEV function in excel is simple to understand. So let’s start learning.

**When to Use AVEDEV function in Excel?**

The word AVEDEV represents Average Deviation or Mean Deviation. The AVEDEV Function in excel is used to compute the average deviation of all the values from the central point. The central point is the **average** of all the numerical values in the data set.

The AVEDEV Function calculates the mean deviation about the mean.

**What is Mean Deviation about Mean?**

Let us suppose we have a set of n numerical values as X_{1}, X_{2}, X_{3}………..X_{n}.

We can calculate the mean of these values as:-

Mean ( x̄ ) = (X_{1}+ X_{2}+ X_{3}………..+X_{n)}/n.

Thereafter, we would calculate the deviation of n terms X_{1}, X_{2}, X_{3}………..X_{n} from the mean x̄.

D_{1} = |X_{1}-x̄|, D_{2} = |X_{2}-x̄|, D_{3} = |X_{1}-x̄|……..D_{n} = |X_{n}-x̄|

There mean absolute values of these deviations D1, D2, D3………..Dn is known as mean deviation about the mean.

MD(x̄) = (D_{1}+ D_{2}+ D_{3}………..D_{n})/n

All these calculations can be replaced with the AVEDEV function of excel.

**Syntax and Arguments**

**=AVEDEV(number1,[number2],[number3]……)**

The following points would explain to you the required function arguments for the AVEDEV Function of Excel.

This can be a cell reference, a range of cells or a*number1 –***named range**containing the set of numbers. Alternatively, we can also directly pass the hard-coded numerical value.This is an optional argument and works the same as the*[number2] –**number1*argument.

Moreover, there can be a maximum of 255 *numbers*. Only the first number *number1 *is mandatory to specify.

**Examples to Learn AVEDEV Function of Excel**

In this part of the blog, we would perform some examples to implement the AVEDEV Function.

**Example 1 – Mean Deviation of Marks in Class**

Let us suppose we have a list of marks of students in a class test as follows:-

We want to calculate the mean deviation for the marks of students in this class test.

Use the following formula to get the value of the mean deviation about the mean.

=AVEDEV(A2:A11)

As a result, the AVEDEV Function returns the value of the mean deviation of the marks as 2.6

**Explanation ** – We have passed the range A2:A11 as the *number1 *argument of the AVEDEV function. There are 10 numerical values in this range which would act as X_{1}, X_{2}, X_{3}………..X_{10}.

The function calculated the mean deviation by using its formula implicitly and we got the result.

**Result Verification of AVEDEV function**

We can verify the result of the above AVEDEV formula by implementing the formula of mean deviation about the mean. The following are the steps involved.

**Step – 1****Calculating the Mean of Values**

We are given the set of numerical values as the marks of students in the class test. We would use the **AVERAGE function formula** to get the average marks. Use the following formula in cell D4.

=AVERAGE(A2:A11)

**Step 2 – Calculating Absolute Deviation**

We would now calculate the absolute deviation of each numerical value from the mean value calculated in cell D4.

Use the following formula in cell B2 and copy it down the range B2:B11.

=ABS(A2-$D$4)

We have **locked cell D4** so that it does not change when we copy down the formula.

The **ABS function** returns the positive deviation of all marks from the mean marks.

**Step 3 – Calculating the Mean of Deviation**

We have calculated the deviations of the class test marks from the mean marks in the range B2:B11. Now we can calculate the Average Deviation by using the following formula.

=AVERAGE(B2:B11)

The Mean Deviation comes out to be 2.6 which verifies our result.

**Points to Remember about AVEDEV Formula**

We must keep the following points in mind while using the AVEDEV formula,

- The AVEDEV formula ignores empty cells, and cells containing non-numerical values.
- The AVEDEV formula returns a #NUM! error if we do not specify any numerical value to the function.
- However, a #VALUE! error is returned if we pass a text string in double quotes as the
*number*argument.

Consequently, this brings us to the end of the AVEDEV formula blog.

Thank you for reading.

## RELATED POSTS

- VAR.P Function in Excel – A Statistical Function
- NORM.DIST Function in Excel – Get PDF and CDF
- AVERAGEA Function in Excel – Finding Average Value From All
- AVERAGE Function in Excel – Finding Mean or Average Value in Excel
- HARMEAN Function in Excel – Get Harmonic Mean
- VAR.S Function in Excel – Variance of Sample