Syntax. AVEDEV(number1,number2,...)
Definition. This function returns the average of the absolute deviation of data points from their mean. The function calculates the arithmetic mean of the deviations of a data set based on the average, excluding the sign. AVEDEV() is a measure of the variance in a data set.
Arguments
number1 (required) and number2 (optional). At least one and up to 255 arguments (30 in Excel 2003 and earlier versions) for which you want to calculate the absolute deviation. You can also use a single array or a reference to an array instead of arguments separated by commas.
Note
Arguments can be numbers, names, arrays, or references containing numbers. If an array or a reference argument contains text, logical values, or empty cells, those values are ignored. However, cells with the value 0 are included.
AVEDEV() is influenced by the unit of measurement in the input data.
Background. To calculate the deviation of sales or, as in our example, the monthly website visits related to the mean, use the AVEDEV() function. AVEDEV() is a measure of the variance in a data set.
In a sense, measures of dispersion are a quality criterion for the measure of central tendency. These measures indicate the accuracy of a measure of central tendency. Variance parameters refer to the difference between the following:
Location values (range, quartile, or semiquartile distance)
Individual values and a mean (average linear deviation, variance, standard or deviation)
Although the measure for the average deviation is easy to calculate, the standard deviation and variance are most commonly used. The equation for the average deviation is:
Example. The marketing department of the software company wants to analyze the customers’ website visits. The visits to the various website areas in the past 18 months are recorded in an Excel table (see Figure 12-1).
Because the average deviation refers to the mean values in the data sets, the marketing department calculates the mean value for each website area by using the AVERAGE() function. Afterwards, the marketing department calculates the average deviation for each data set. The AVEDEV() function returns the results—the arithmetic mean of the deviation from the mean value.
Now the mean values and average deviations can be compared and analyzed. The following statements can be made from this result: The AVEDEV() function is a measure of the variance in a data set where the variance parameters refer to the differences between single values and mean values. The average deviation for the DOWNLOAD area is 378.3 for each month. This means that, compared with the calculated mean value, the visits of the DOWNLOAD area vary by 378.3 per month.