The Excel AVEDEV function returns the average of the absolute value of deviations from the mean for a given set of data. Average deviation is a measure of variability.
Syntax:= AVEDEV (number1, [number2], …)
The AVEDEV function syntax has the following arguments:
- Number1, number2, … Number1 is required, subsequent numbers are optional. 1 to 255 arguments for which you want the average of the absolute deviations. You can also use a single array or a reference to an array instead of arguments separated by commas.
Example: Let’s look at some Excel AVEDEV function examples and explore how to use the AVEDEV function as a worksheet function in Microsoft Excel:
A company keeps a record of its monthly sales figures, over three years. These are stored in cells B3-B14, D3-D14 and F3-F14 of the spreadsheet on the right.
The average deviation of the three years’ sales figures is calculated in cell H3 of the spreadsheet. As shown in the formula bar, the formula for this is:
Syntax: =AVEDEV(B3:B14,D3:D14,F3:F14)
Result: 2032.407407
Based on the Excel spreadsheet above, the following AVEDEV examples would return:
Syntax: =AVEDEV(B3:B14)
Result: 1715.277778
Syntax: =AVEDEV(D3:D14)
Result: 2423.611111
Syntax: =AVEDEV(F3:F14)
Result: 2027.777778
Syntax: =AVEDEV(B3:B14,D3:D14,F3:F14)
Result: 2032.407407
Note:
- AVEDEV is influenced by the unit of measurement in the input data.
- Arguments must either be numbers or be names, arrays, or references that contain numbers.
- Logical values and text representations of numbers that you type directly into the list of arguments are counted.
- If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
- The equation for average deviation is:
