AVEDEV Function in Excel – Definition and Examples

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₁, X₂, X₃………..X_n.

We can calculate the mean of these values as:-

Mean ( x̄ ) = (X₁+ X₂+ X₃………..+X_n)/n.

Thereafter, we would calculate the deviation of n terms X₁, X₂, X₃………..X_n from the mean x̄.

D₁ = |X₁-x̄|, D₂ = |X₂-x̄|, D₃ = |X₁-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₁+ D₂+ D₃………..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.

• number1 – This can be a cell reference, a range of cells or a named range containing the set of numbers. Alternatively, we can also directly pass the hard-coded numerical value.
• [number2] – This is an optional argument and works the same as the 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₁, X₂, X₃………..X₁₀.

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.