How to use AVERAGEIF function in Excel

To calculate the average of the data in the table on Excel, we will use the AVERAGE function, the familiar average calculation function. However, almost calculating the average value on Excel will come with many different conditions. And so, you need the AVERAGEIF function to be conditionally averaged.

AVERAGEIF will proceed with the average plus the zoned data areas in the table, along with the conditions that the user set. How to use the AVERAGEIF function to calculate conditional averages on Excel will be guided by Network Administrator in the article below, through each specific example.

1. These are the most basic functions in Excel that you need to understand
2. Forgot password protected Excel file, what should you do?

The AVERAGEIF function syntax on Excel is: = AVERAGEIF (range, criteria, [average_range]) .

Inside:

1. Range is the range for which the average value can be calculated, including numbers, names, references, arrays.
2. Criteria are conditions in the form of numbers, expressions, cell references or text to determine the average value. Conditions are in "".
3. Average_range includes cells that need to calculate the average value. If Average_range is empty, it will replace with Range to calculate the average value.

Note when using AVERAGEIF function :

1. Empty cells in Average_range, TRUE, FALS and AVERAGEIF functions will be ignored.
2. If Range does not contain a value or contains a string function, the result will indicate an error.
3. If the Criteria is empty, the function is 0.
4. If we determine that there is no value matching the criteria, we will get an error.
5. Average_range does not need to be mandatory in the size of the Range range. If not, the AVERAGEIF function will determine from the top left cell as the first cell and then match the range.

We will proceed to calculate the average score in the data table below.

How to use AVERAGEIF function in Excel Picture 1

Example 1 : Calculate the average score of students with scores greater than 5 .

Enter the formula = AVERAGEIF (D2: D6, "> 5") and press Enter.

How to use AVERAGEIF function in Excel Picture 2

The result will look like the image below.

How to use AVERAGEIF function in Excel Picture 3

Example 2 : Calculate the average score of students with scores less than 5 .

Enter the formula = AVERAGEIF (D2: D6, "<5") and press Enter.

How to use AVERAGEIF function in Excel Picture 4

The end result is # DIV / 0! Because Excel relies on user-defined conditions and has no value corresponding to that condition, there are no students below point 5.

How to use AVERAGEIF function in Excel Picture 5

Example 3 : Calculate the average of male sex students .

Here you need to delineate 2 areas as Gender to filter the condition and the Score area to calculate the average score.

The syntax used here is = AVERAGEIF (C2: C6, "Male", D2: D6) , press Enter to perform.

How to use AVERAGEIF function in Excel Picture 6

The end result will look like the image below.

How to use AVERAGEIF function in Excel Picture 7

Example 4 : Calculate the average of students who do not have the Thi in the Last and Last names column .

We will have the formula = AVERAGEIF (B2: B6, "<> Marketing", D2: D6) and press Enter.

How to use AVERAGEIF function in Excel Picture 8

The result is the average of the classification score by name as shown below.

How to use AVERAGEIF function in Excel Picture 9

The above is a tutorial on how to use the AVERAGEIF function with a common usage syntax, and each case with different conditions. You need to specify the area to be calculated as well as the conditions to apply the AVERAGEIF function accordingly.

I wish you all success!