Function AVERAGEIF combines the function IF and function AVERAGE in Excel; this combination allows you to find the mean or arithmetic mean of these values in a selected range of data that meets certain criteria.
Part IF function determines which data meets the specified criteria, and shares AVERAGE calculates the mean or mean value. Usual AVERAGE IF used with sets of data called records. In a record, all the data in each cell is linked in a row.
The information in this guide applies to Microsoft Excel 2007, 2010, 2016 and the latest version of Office 365.
- Data Entry Tutorial
- AVERAGEIF Function Syntax
- Running the AVERAGEIF function
- Enter a range argument
- Criteria Entry
- Enter the Average_Range argument
- Add search criteria
Data Entry Tutorial
By following the instructions in this tutorial you will learn how to create and use a function AVERAGEIF to calculate the average annual sales in the sample data set. Through this process, you will learn how to set the job criteria and how to average the given data.
Start entering the sample data in cells C1 in E11 blank excel spreadsheet as shown in the image above.
The tutorial instructions do not include formatting steps for the worksheet. Your worksheet will be different from the worksheet in the example, but the function AVERAGE IF gives you the same results.
AVERAGEIF Function Syntax
In Excel, function syntax refers to the layout of the function and includes the function name, parentheses, and arguments. Syntax for AVERAGEIF †
= AVERAGEIF (диапазон, критерии, средний диапазон)
The function arguments tell the function what condition to test against and what range of data to average when that condition is met.
Range: † The group of cells for which the function is intended.
criteria † Value compared to data in reach † If a match is found, the corresponding data is entered in Average_range be averaged. For this argument, you can enter actual data or a reference to a data cell.
Average_range (not necessary). The data in this range of cells is averaged when matches are found between the arguments Range And criteria † If the argument Average_range omitted, data matching the argument Range be averaged.
Running the AVERAGEIF function
Although you can enter a function in a cell AVERAGEIF many people find it easier to use Formula Builder to add the function to the sheet.
Press cell E12 to make it the active cell – here we enter the function AVERAGEIF †
Press Formulas Tab on the adhesive tape †
Select Additional Features > Statistical on the ribbon to open the features drop-down list.
Click AVERAGEIF in the list.
The data we enter in the three blank lines in Formula Builder will form function arguments AVERAGEIF † these arguments tell the function what condition we are testing on and what range of data we average when the condition is met.
Enter a range argument
In this lesson, we look at the average annual sales in the Eastern sales region. The Range argument tells the function AVERAGEIF which group of cells to search for when searching for the specified criteria.
IN Formula Builder click on the line Range †
highlight cells C3 before C9 on the worksheet to enter those cell references as the range the function looks for.
In this example, if the data is in the range C3:C12 Directly East then the total sales for that record must be averaged by the function.
While factual data such as the word East , you can type in the dialog for that argument, it is usually better to add the data to a cell on the worksheet and then enter that cell reference into the dialog. †
Click on a line criteria †
Click cell D12 to enter a reference to this cell – the function searches the range selected in the previous step for data that meets this criterion.
Search criteria ( East ) is added to cell D12 in the final stage of the course.
Cell references increase feature versatility
If a cell reference, for example D12 entered as criteria argument, the function AVERAGEIF searches for matches with all data entered in that cell on the worksheet.
So, after determining the average sales for the eastern region, it will be easy to find the average sales for another sales region by simply changing East on the north or West † The function is automatically updated and the new result is displayed.
Enter the Average_Range argument
Argument Average_range is the group of cells that the function should average when it finds a match in its argument Range † this argument is optional, and if not specified, Excel averages the cells specified in the argument Range †
Because we want to get the average sales for the sales region East we use the data in the Total revenue column as argument Average_range †
Click on a line Average_range †
highlight cells E3 on the E9 in a spreadsheet. If the criteria specified in the previous step match any of the data in the first range (from C3 before C9 ), the function will average the data in the corresponding cells in the second range of the cell.
Click Finished to complete the function AVERAGEIF †
# DIV/0! error will appear in cell – the cell where we entered the function, because we haven’t added any data in the Criteria field ( D12 †
Add search criteria
The last step in the tutorial is to add the criteria that the function must meet. In this case, we want to determine the average annual sales of sellers in the Eastern region, so we add the term . please East in cell D12 † – cell identified in the function as containing the criteria argument.
IN cell D12 enter East and press the key Enter on keyboard.
Reply $59,641 should appear in cell E12 since the equality criterion East corresponds to four cells ( C3 † C6 ) and the numbers in the corresponding cells in column E (from E3 before E6 ).>) are averaged.
= AVERAGEIF (C3: C9, D12, E3: E9)
When you press cell E12 the full function appears in the formula bar above the worksheet.
For example, to find the average sales value for other sales regions, enter the region name north in cell E12 and press Press key on keyboard. The average value for this sales region should appear in cell E12 †