AVEDEV()

SyntaxAVEDEV(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

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:

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:

image with no caption

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).

Mean value and average deviation for the website visits.

Figure 12-1. Mean value and average deviation for the website visits.

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.

See Also

AVERAGE(), AVERAGEA(), DEVSQ(), STDEV.S(), STDEVP(), VAR.P(), VAR.S()