AVEDEV Excel Formula

Author Zaheer    Category Formulas     Tags ,

Excel Statistical Functions has been part of our daily computation process. These calculations can be used in computing the mean, median, and mode and other complex statistical calculations. Some of the functions may not be readily available in earlier versions of Excel.

One of the Statistical Functions in Excel is the AVEDEV function. This function allows you to calculate the average deviation of a given set of numerical values. The syntax of the AVEDEV function is AVEDEV(number1,number2,number3…). In the syntax, the argument number1,number2,number3… are numerical values or can also be source cell that contains numerical values, names, or arrays.

Moreover, the AVEDEV relies on the measurement of the given numerical data. The source argument that contains logical values, non-numeric values, or are empty it will not be included I the argument but those with 0 values are counted.

There is also limited number of arguments that you can enter into the syntax. For the Excel 2007 and Excel 2010 the maximum number of arguments is 255. for the Excel 2003 the maximum number of arguments is only 30.

Example, if you have a company and you want to know the average deviation of your sale for a certain number of years you can use the AVEDEV function. Input the values in the cells of an Excel spreadsheet indicating the date or month and year of the sales. For example, input the dates in cell A2-A14 and beside it, in cell B2-B14 are the amount of sales for the months in the given year. Do the same in cell C2-C14 for the second year and indicate the amounts in cell D2-D14. For the third year input the months in cell E2-E14 and the amounts in cell F2-F14.

After inputting the data, on cell H3 input the formula =AVEDEV(B3:B14, D2:D14, F2-F14) hit enter and the result will automatically show. To view the formula you can select the cell and on the Formula bar the formula will appear.

You can also add another argument in the function in a manner of numerical figures. For example, you want to add sales for the month of January and February, you can do this by simply inputting the numerical values as =AVEDEV(B3:B14, D2:D14, F2-F14, 12000, 1300). The value in H3 will automatically change.

There are also common errors that will occur during the usage of the AVEDEV function. The #DIV/0! error occurs when the values given in the function are in numerical form. Also, the #VALUE! error will occur if the given non-numerical values in the function cannot be interpreted as numeric values.

Post comment