Related Functions:

The AVEDEV function calculates the average deviation of a supplied set of values.

Syntax

=AVEDEV(num_1,[num_2], …)

  • Beginning with Excel 2007, you can enter up to 255 number arguments to the function. Excel 2003 would only accept up to 30 number arguments.

Arguments

Argument Description
num_1,
[num_2], …
The arguments, num_1, [num_2], etc, are one or more numerical values or references to cells containing numbers

Note: Arguments to the AVEDEV function can be input as cell ranges, individual numbers, or number arrays, e.g., in the table below:

  • The result in J3 uses a range of cells (see K3)
  • The result in J4 uses cell ranges plus individual numbers (see K4)
  • The result in J5 uses cell ranges plus a number array (see K5)

Examples

  A B C D E F G H I J K
1 2010 2011 2012 2013   Average
Deviation:
 
2 Month Amount Month Amount Month Amount Month Amount    Formula used
3 Jan-10 15,000 Jan-11 17,500 Jan-12 13,000 Jan-13 13,000   2,027.78 =AVEDEV(B3:B14,D3:D14,F3:F14)
4 Feb-10 14,500 Feb-11 12,000 Feb-12 15,000 Feb-13 14,500   1,973.68 =AVEDEV(B3:B14,D3:D14,F3:F14,13000,14500)
5 Mar-10 14,500 Mar-11 16,000 Mar-12 14,000       -or- =AVEDEV(B3:B14,D3:D14,F3:F14,{13000,14500})
6 Apr-10 14,000 Apr-11 19,000 Apr-12 16,500          
7 May-10 16,000 May-11 17,000 May-12 20,000          
8 Jun-10 9,500 Jun-11 10,500 Jun-12 12,500          
9 Jul-10 13,500 Jul-11 11,000 Jul-12 14,000          
10 Aug-10 17,000 Aug-11 12,500 Aug-12 18,500          
11 Sep-10 11,000 Sep-11 13,000 Sep-12 14,500          
12 Oct-10 15,000 Oct-11 15,500 Oct-12 13,000          
13 Nov-10 17,500 Nov-11 15,000 Nov-12 13,000          
14 Dec-10 18,000 Dec-11 17,500 Dec-12 17,000          

Usage note:

  • the function ignores logical values and text values, including text representations of numbers, if these are supplied as part of an array of cells
  • the function does count logical values and text representations of numbers that supplied directly to the function

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any supplied values are text values that cannot be interpreted as numeric values
#DIV/0! Occurs if none of the values supplied to the function are numeric

The Average Deviation, like the Standard Deviation, measures the deviation of a set of points from their average.

The Average Deviation is calculated by the following equation:

     \[\text{Average Deviation} = \frac{1}{n} \sum |x-\bar{x}| \]

where  x  takes on each value in the set,  x  is the average (statistical mean) of the set of values, and  n  is the number of values.