< Go Back

Excel AVERAGEIF Function

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.

General formula

=AVERAGEIF(range,criteria,[average_range])

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

=AVERAGEIF(F2:F18,“>55”)

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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar