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.
Syntax of AVERAGEIFS is also same as AVERAGEIF but it asks multiple like criteria_range1,criteria1, criteria_range2,criteria2 etc. Lets understand it from the below example.
In the above example we took 2 criteria(greater than 40 and less than equal to 80) to filter our data. in criteria1 we used >40 while in criteria2 <=80. We get average of all numbers which falls between these two numbers. Now to confirm this we will create a new column and will remove all numbers which fulfill these criteria. After doing this we will use AVERAGE function to get the average of these numbers. Now in both these scenarios we will get 59.33.
That’s all for now, will meet again with some new topic.
You can read more about average function from the below article.
https://exceljet.net/excel-functions/excel-averageifs-function