In this guide we have to discuss how could you easily calculate the arithmetic mean of different data types with the help of Excel AVERAGEIF function. The AVERAGEIF function in Excel is used to returns the average (arithmetic mean) of all the cells with in the range which meet the given criteria.
Average is also called arithmetic mean or mean. You can easily calculated the mean by adding a group of numbers together and then dividing by the count of those numbers. If you want to calculate simple average then you have to use AVERAGE function in Excel. But if you want to calculate the average cells which is based on a given criteria then you must have to use Excel AVERGAEIF function. The AVERAGEIF function is located in Statistical category in Excel.
How to use COUNTIFS and SUMIF together in Excel
For eg: If you have a employee database in which you have Emp Code, Name, Designation, Overall Sales and New Products (NPD) sales amount. If you want to calculate the average of Overall sales amount of all employees than you have to use given AVERAGE function =AVERAGE(D2:D11).
You have to select range of continuous cells which average you want to calculate. But if you want to calculate average only all those employee’s which designation match with “SR”. In that situation you must have to use AVERAGEIF function at the place of AVERAGE function in Excel.
Syntax of AVERAGEIF Function
AVERAGEIF(range, criteria, [average_range])
The AVERAGEIF function has following arguments, the first two arguments are compulsory and last one is optional.
Range – Required argument. The range of cell is tested against the given criteria. You can choose any one or more cells including the numbers, arrays, or cell references which contains the numbers.
How to lookup images in Excel using Index Match function
Criteria – It is a required argument. The criteria express which cells to average. You can use any number, expression, cell address, or any text.
Average_range – It is an optional argument.
Step to use the Excel AVERAGEIF function
The Excel AVERAGEIF function allows you to calculate the AVERAGE of all those cells which is based on a given criteria.
Step 1: To calculate the AVERAGE of Overall sales amount of those employees which designation match with “SR”. In that situation you have to apply the given function in Cell D14 =AVERAGEIF($C$2:$C$11,$D$13,$D$2:$D$11).
Step 2: To calculate the AVERAGE of NPD sales amount of “SR” employees. In that situation you have to apply the given function in Cell D15 =AVERAGEIF($C$2:$C$11,$D$13,$E$2:$E$11).
In this guide you can easily understand how could you easily calculate the average in Excel. If you have any doubt regarding this article then let me know by sending you query in comment box. Thanks to all.