In Excel, one get the arithmetic mean of cells in a given range using the Excel AVERAGEIF function. This function works when there is a specific supplied criterion.
Note that the criteria used with this function can support logical operators as well as wildcards.
When used correctly, this function will return a number which represents the average of the specified data.
There are three arguments in the formula as discussed below:
- Range- this refers to cells, arrays or references. The cells can contain numbers or even names.
- Criteria- a number, cell reference, text or expression.
- Average_range- this shows the average of the cell. In the event that you omit this one, the range will be used instead. Note that this is not mandatory for the function to work correctly.
How the Excel AVERAGEIF function works
To understand how excel AVERAGEIF function works, we have to look at the following example;
Example 1: using excel AVERAGEIF to find the average of sales greater than 55
Figure 1: Worksheet of sales entries in selected days
In the above worksheet, we want to find the average of sales that are above 55. We proceed as follows.
Step 1: Click in an empty cell where the result should be displayed.
Step 2: specify the function
aFigure 2. Using AVERAGEIF function to find average revenue
Step 3: Press Enter
When you press Enter, you will have the results displayed. In our case, the average of all the entries above 55 is 86.33333333
Things to remember
- When using the Excel AVERAGEIF function, cells that contain TRUE /FALSE are not considered.
- Cells that are empty are also ignored
- If no cell meets the specified criteria, then the AVERAGEIF will return #DIV?)!
- When working, you don’t have to worry about average_range being of the same size as a range. They can be different.
- Put simply, the Excel AVERAGEIF function is so essential especially when we want to find the averages of selected entries in a worksheet. Without this function, the whole work will be tedious and difficult.