Excel AVEDEV Function

What is AVEDEV function in Excel?

The AVEDEV function is one of the Statistical functions of Excel.

It Returns the average of the absolute deviations of data points from their mean.

Arguments can be numbers or names, arrays, or references that contain numbers.

We can find this function in Statistical category of insert function Tab.

How to use AVEDEV function in excel

1. Click on an empty cell (like F5).

2. Click on the fx icon (or press shift+F3).

3. In the insert function tab you will see all functions.

4. Select STATISTICAL category.

5. Select AVEDEV function.

6. Then select ok.

7. In the function arguments Tab you will see AVEDEV function.

8. Number1: number1,number2,… are 1 to 255 arguments for which you want the average of the absolute deviations.

9. You will see the results in the formula result section.

Examples of AVEDEV function in Excel

example1:

How to calculate the average of absolute deviations in Excel?

The process for finding the mean absolute deviation involves the following steps.

1.get numbers.

numbers=23,30,31,15,46

2. calculate sum of numbers.

=SUM(23,30,31,15,46) ----->>>>answer is  145

3. calculate count of numbers.

=COUNT(23,30,31,15,46) ----->>>>answer is  5

4. calculate average of numbers.

=AVERAGE(23,30,31,15,46) ----->>>>answer is  29

5. calculate deviation from average numbers.

=ABS(23-29) ----->>>>answer is  6
=ABS(30-29) ----->>>>answer is  1
=ABS(31-29) ----->>>>answer is  2
=ABS(15-29) ----->>>>answer is  14
=ABS(46-29) ----->>>>answer is  17

6. calculate sum of deviations.

=SUM(6,1,2,14,17) ----->>>>answer is  40

7. calculate the average of absolute deviations.

=SUM(6,1,2,14,17)/COUNT(23,30,31,15,46) ----->>>>answer is  8

All these steps are summarized in the following function.

=AVEDEV (23,30,31,15,46) ----->>>>answer is  8

What is the purpose of AVEDEV function?

Calculating the absolute deviation is a crucial step for determining what the average deviation is. The absolute deviation is the difference between a data set’s mean and each value in the respective data set.

The purpose of the AVEDEV function in Excel is to calculate the average deviation of a set of data points from their mean value. The function measures how far each data point is from the mean of the entire sample, takes the absolute value of those distances, and then calculates the average of those absolute values.

The AVEDEV function is useful for analyzing the spread or variability of a set of data points. It provides a measure of the average distance between each data point and the mean of the entire sample, which can help identify outliers, trends, or other patterns in the data.

By calculating the average deviation of a set of data points from their mean value, the AVEDEV function can help you:

1. Identify the spread or variability of the data
2. Evaluate whether the data is evenly distributed around the mean or skewed in one direction
3. Compare the variability of different sets of data
4. Determine the magnitude of differences between data points and their mean value

Overall, the AVEDEV function is a useful tool for analyzing the distribution and variability of a set of data points, and it can help you make more informed decisions based on your data analysis.

What is the Return value  of AVEDEV function?

It returns a positive number.

The AVEDEV function in Excel returns the average deviation of a set of data points from their mean value. The return value is always a numeric value representing the average absolute distance between each data point and the mean of the entire sample.

For example, if you have a list of numbers and want to calculate the average deviation of those numbers from their mean, you can use the AVEDEV function. The return value will be a single number representing the average absolute deviation of the data points from their mean value.

The return value of the AVEDEV function can be used in further calculations or displayed on its own as a measure of the spread or variability of the data. It can help identify outliers or other patterns in the data that may not be apparent from the raw data points themselves.

AVEDEV (numbers)= number

How many arguments does AVEDEV function have?

The AVEDEV function in Excel takes at least one argument, which is the range of data points from which you want to calculate the average deviation. You can also include up to 255 additional arguments to add more ranges or individual values.

The syntax for the AVEDEV function with multiple arguments is as follows:

=AVEDEV(number1, [number2], …)

Where “number1” is the first numeric value or range of values, and “[number2]” is an optional second numeric value or range of values. You can continue to add as many additional arguments as needed, each separated by a comma.

The maximum number of arguments that the AVEDEV function can take is 255, which includes the initial range of data points and any additional ranges or individual values. If you need to include more than 255 arguments in your calculation, you can split the calculation into multiple formulas or use a different function that supports more arguments.

The argument of AVEDEV function is number.

This function has just 1 Argument.

The argument of AVEDEV function is required and not optional.

AVEDEV(+-number) = +number

Which version of excel supports AVEDEV function?

The AVEDEV function is supported by Microsoft Excel 2007 and later versions, including Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Excel for Office 365. This function is not available in earlier versions of Excel such as Excel 2003 or older. If you are using an earlier version of Excel that does not support the AVEDEV function, you can use alternative functions such as the AVERAGEIF or AVERAGEIFS functions to achieve similar results.

Errors in AVEDEV function

Like any other function in Excel, the AVEDEV function can produce errors if there are issues with the input or formula. Here are some common errors that you may encounter when using the AVEDEV function:

1. #VALUE! error: This error occurs if any cell in the input range contains non-numeric data, such as text or logical values. To avoid this error, ensure that your input range contains only numeric data.
2. #DIV/0! error: This error occurs if the input range contains only one value. Since the AVEDEV function calculates the average deviation of data points from their mean, it requires at least two values to be meaningful. To fix this error, add more values to your input range.
3. #NUM! error: This error occurs if the input range is empty or contains a mathematical error such as division by zero. To fix this error, double-check your input range and ensure that all calculations are correct.
4. #NAME? error: This error occurs if you misspell the name of the AVEDEV function or use incorrect syntax in your formula. Ensure that you spell the function name correctly and follow the correct syntax for the function.
5. #REF! error: This error occurs if the input range includes cells that have been deleted or moved. To fix this error, adjust your input range so that it includes only valid cells.

It’s important to double-check your input data and formula to avoid these errors when using the AVEDEV function. If you encounter an error, try to identify the source of the error by reviewing your inputs and formula, and then make the necessary corrections.

If you enter a non-numeric number, the output show #VALUE! or #NAME? error

AVEDEV(a) ----->>>>answer is   #NAME?

AVEDEV("a") ----->>>>answer is  #VALUE!

AVEDEV function with non-numeric data

AVEDEV function in Excel is designed to work with numeric data only. If you try to use it with non-numeric values, such as text or logical values, you will get a #VALUE! error.

To avoid this error, you can use the AVEDEV.S function instead of the AVEDEV function. The AVEDEV.S function works exactly like the AVEDEV function, but it ignores any non-numeric values in the input range.

Here’s an example of how to use the AVEDEV.S function:

Suppose you have a list of numbers and some non-numeric values in column A, like this:

To calculate the average deviation of the numeric values in column A, you can use the AVEDEV.S function as follows:

1. Select an empty cell where you want to display the result.
2. Type “=AVEDEV.S(” (without quotes).
3. Highlight the range of cells containing your data, including the non-numeric values.
4. Close the parentheses and press Enter.

The formula should look like this: =AVEDEV.S(A1:A6)

The result should be the average deviation of the numeric data points from their mean. Any non-numeric values in the input range will be ignored by the function.

In this example, the result would be approximately 11.18, which is calculated as follows:

• Mean value = (10 + 20 + 30 + 40) / 4 = 25
• Absolute deviations from the mean = |10 – 25|, |20 – 25|, |30 – 25|, |40 – 25| = 15, 5, 5, 15
• Average absolute deviation = (15 + 5 + 5 + 15) / 4 = 11.25

It’s important to note that the AVEDEV.S function should be used with caution. If there are too many non-numeric values in the input range, the result may not accurately reflect the true average deviation of the numeric data points. In this case, it may be necessary to clean the data by removing any non-numeric values before using the AVEDEV function.

Apply the AVEDEV function to a range of cells

To apply the AVEDEV function to a range of cells in Excel, you can use the following steps:

1. Select an empty cell where you want to display the result.
2. Type “=AVEDEV(” (without quotes).
3. Highlight the range of cells containing your data.
4. Close the parentheses and press Enter.

Here’s an example to illustrate how to use the AVEDEV function with a range of cells:

Suppose you have a list of numbers in cells A1 through A5. To calculate the average deviation of these numbers from their mean using the AVEDEV function, follow these steps:

1. Select an empty cell where you want to display the result, such as cell B1.
2. Type “=AVEDEV(” (without quotes).
3. Click on cell A1 and drag to highlight cells A1 through A5.
4. Close the parentheses and press Enter.

The formula should look like this: =AVEDEV(A1:A5)

The result should be the average deviation of the data points in cells A1 through A5 from their mean.

It’s important to note that the AVEDEV function is sensitive to outliers, so it may not always provide an accurate measure of variability for your data. In some cases, it may be better to use other statistical functions such as STDEV or MAD (Median Absolute Deviation) to measure the spread of your data.

applications of AVEDEV function

The AVEDEV function in Excel is commonly used to measure the spread or variability of a set of data points. Here are some common applications of the AVEDEV function:

1. Financial analysis: The AVEDEV function can be used to calculate the average deviation of a stock’s price from its mean value over a period of time.
2. Quality control: The AVEDEV function can be used to determine the average deviation of product measurements from their target values, which can help identify defects or other quality issues.
3. Scientific research: The AVEDEV function can be used to measure the variability in experimental data, such as physiological or environmental measurements.
4. Sports analytics: The AVEDEV function can be used to analyze player performance statistics, such as the average deviation of a basketball player’s shooting percentage from their mean shooting percentage.
5. Education: The AVEDEV function can be used to evaluate student performance on assessments by measuring the variability in their scores.

Here’s an example to illustrate how the AVEDEV function can be used in financial analysis:

Suppose you have a list of daily closing prices for a stock over a 30-day period, and you want to calculate the average deviation of the stock’s price from its mean value. You can use the AVEDEV function as follows:

1. Select an empty cell where you want to display the result.
2. Type “=AVEDEV(” (without quotes).
3. Highlight the range of cells containing your data, such as cells A1 through A30.
4. Close the parentheses and press Enter.

The formula should look like this: =AVEDEV(A1:A30)

The result will be the average deviation of the stock’s price from its mean value over the 30-day period.

In summary, the AVEDEV function can be applied in a variety of fields to measure variability in data sets. It provides a useful measure of the spread of data points from their mean value and can help identify patterns or trends in data.

accuracy of the results obtained from the AVEDEV function?

The AVEDEV function (short for “average deviation”) is a statistical function that calculates the average of the absolute deviations from the mean of a set of values. The result obtained from the AVEDEV function can be considered accurate in the sense that it provides a measure of how much the values in the set differ from the mean value. However, like any statistical function, the accuracy of the result depends on the quality and reliability of the input data.

If the input data is representative of the population or phenomenon being studied, and there are no significant errors or outliers in the data, then the result obtained from the AVEDEV function can be considered quite accurate. On the other hand, if the input data is biased or incomplete, or contains significant errors or outliers, then the result obtained from the AVEDEV function may not accurately reflect the true variability of the data.

In summary, the accuracy of the results obtained from the AVEDEV function depends on the quality and representativeness of the input data, as well as the absence of significant errors or outliers in the data.

AVEDEV function different from other statistical functions

The AVEDEV function is a statistical function in Excel that calculates the average of the absolute deviations from the mean of a set of values. It is different from other statistical functions in Excel in several ways:

1. AVEDEV calculates the average deviation from the mean, while other statistical functions like VAR, STDEV and STDEVP calculate the variance and standard deviation respectively.
2. AVEDEV takes into account all values in the dataset, while other statistical functions may exclude some values based on certain criteria or assumptions.
3. AVEDEV is less sensitive to extreme values (outliers) than other measures of dispersion like the standard deviation.
4. AVEDEV can be used as a robust alternative to the standard deviation in cases where the data contains outliers or is not normally distributed.

In summary, the AVEDEV function provides a measure of the variability of a dataset that is different from other statistical functions in Excel, and can be useful in situations where other measures are not appropriate or may be affected by outliers or non-normal distributions.

AVEDEV related functions 

• Use AVERAGE function to return the average of its arguments, which can be numbers or names, arrays, or references.
• STDEV function
• DEVSQ function

Related posts:

Excel LOGNORM.INV Function Excel AVERAGE Function Excel HARMEAN Function Excel LOGNORM.DIST Function