# 5 Excel functions for average: 5 Useful functions in excel to calculate average || DataWitzz

#### 5 Excel functions for average

We often require to find the average score of data sets in our regular excel use. Even sometimes we need to find the average basis of some criteria. Excel provides multiple functions to calculate the average. We will be discussing the following average function in this article.

• AVEDEV
• AVERAGE
• AVERAGEA
• AVERAGEIF
• AVERAGEIFS

Let’s understand all of them one by one

AVEDEV is used to get the return of the average of the absolute deviations of data points from their mean. AVEDEV is basically a measure of the variability in a data set. Syntax for AVEDEV will be as given below.

LLets understand this function from the below example as illustrated in the image.

In the above example we can see that we first find absolute deviation by using ABS function with average of the data set minus each data set(formula is in cell E2) then used average function to find out average of absolute deviation. Again we use AVEDEV function in D15 and both value comes same as 23.28.

AVERAGE function is the most used function in excel to find out average of any data set. Syntax of AVERAGE is as given below.

In the above example we can see this function asks number1,number2 etc which is basically datasets. Lets understand this by an example.

In the above example we can see we used AVERAGE function of excel and used data set as C2 to C11. it gives us average of that dataset. We can also use sum of the dataset and then divide it by total number of data points. The final value will remains same.

AVERAGEA is very useful function when we consider text and numeric both in the dataset. It works like AVERAGE function of excel but there is a difference between the both. The difference is that AVERAGEA treats text as 0 value while AVERAGE value ignores text. Systax for AVERAGEA remains same as AVERAGE function. Lets understand this by an example illustrated in the below image.

In the above example we see our data set is in C2 to C11 which contains a text value. Now we will use our AVERAGEA function and find a value of 65.1. Now to check if really AVERAGEA treats text as 0,we will create a new dataset and replace text value with 0. After that we will use AVERAGE function to get average value. The final value comes the same as we found in AVERAGEA.

AVERAGEIF is a variation of AVERAGE function but it gives an option to use some criteria to filter the dataset before finding the average. It’s syntax is given below.

Syntax of AVERAGEIF has three inputs:

range : range of dataset

criteria: Criteria by which data needs to filter

[Average_Range] : It is optional and used to select the actual set of range for average. If we ommit it then range will be automatically selected.

Now lets understand it by an example

In the above example we wanted to find average of all scores which is greater than 40. To find this we will use AVERAGEIF as mentioned above. Now we got score of 78.25.Let verify this by committing all scores which is less than or equal to 40 and will use AVERAGE function. The values comes the same as came earlier.

AVERAGEIFS it also works like AVERAGEIF but it can take multiple conditions to filter as compared to AVEREGIF. Lets understand its syntax.