How to Calculate Average If Criteria Not Blank/Ignore Blank Cell

In daily work we often need to calculate the average of some numbers based on given conditions or criteria. To calculate average with criteria, we can apply AVERAGEIF of AVERAGEIFS function. AVERAGEIFS function can handle more than one group of criteria range and criteria. In this article, we will show you how to calculate average of numbers whose corresponding cells in another group are not blank. Thus, we need to know some knowledge of AVERAGEIFS function.

In this article, we will let you know the syntax, arguments, and basic usage about AVERAGEIFS function, and apply it to build a formula, also let you know the calculation steps of the formula.

EXAMPLE

How to Calculate Average If Criteria Not Blank 1

Refer to “Score” column, some numbers are listed in range B2:B10. “Level” column is used for providing a level based on scores. In E2 and F2, we calculate the average of all scores and qualified scores separately. To calculate average of all scores, we can directly use AVERAGE function. To calculate average of qualified scores (cell is not blank in Level group), we need to add criteria to filter them, we can apply AVERAGEIFS function, it is also one of the most common used functions in Excel.

First, In C2, enter “=AVERAGE(Score)”. Then press Enter, AVERAGE function returns 73.33333333.

How to Calculate Average If Criteria Not Blank 2

You can adjust decimal places by “Increase Decimal” or “Decrease Decimal” in “Number” section.

How to Calculate Average If Criteria Not Blank 11

Keep clicking on “Decrease Decimal” button till decimal places is ok for you. Keep two decimal places in this case.

How to Calculate Average If Criteria Not Blank 3

Now we can apply AVERGAEIFS function to calculate the average of qualified scores.

Before creating the formula, name range “B2:B10” to “Score”, “C2:C10” to “Level” in Name Box.

How to Calculate Average If Criteria Not Blank 4

CREATE A FORMULA with AVERAGEIF FUNCTION

1. STEPS

Step 1: In F2, enter the formula =AVERAGEIFS(Score,Level,”<>”).

How to Calculate Average If Criteria Not Blank 5

Step 2: Press Enter after typing the formula.

How to Calculate Average If Criteria Not Blank 6

Only B2, B4, B5, B6, B8, B9 and B10 meet our given criteria “level is A or B or C”, so we calculate the average of numbers in above cells, total 7 numbers. (60+62+90+83+88+74+90)/7=78.14285714, keep two decimal places 78.14. The formula works correctly.

2.FUNCTION INTRODUCTION

AVERAGEIFS function is AVERAGE+IFS. It returns the average of some numbers in a range based on one or more given conditions or criteria.

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2],…)

It supports wildcards like asterisk ‘*’ and question mark ‘?’, also supports logical expressions like ‘>=’,’<=’. If wildcards or logical operators are required to build criteria, they should be enclosed into double quotes (““). In this case we entered “<>” to represent criteria.

3.ALL ARGUMENTS

AVERAGEIFS – Average Range

This example is very simple, we have only one average range A2:A10.

In the formula bar, select named range “Score”, press F9, values in this range are expanded in an array.

How to Calculate Average If Criteria Not Blank 7

AVERAGEIF – Criteria Range

The criteria range is C2:C10.

In the formula bar, select named range “Level”, press F9, values in this range are expanded in an array.

How to Calculate Average If Criteria Not Blank 8

AVERAGEIF – Criteria

The criteria in our case is “<>”, in this case it represents cells are not blank. As logical expression is used, so we add “” to quote “<>”.

How to Calculate Average If Criteria Not Blank 9

4.EXPLANATION

After expanding values, the formula is displayed as:

=AVERAGEIFS({60;55;62;90;83;58;88;74;90},{"C";0;"C";"A";"B";0;"A";"C";"A"},"<>")

Note: 0 is recorded in this array to represent blank cells.

In the formula, the criteria is “<>” (“not equals to” operator), based on this criteria, cells without any level in “Level” column cannot meet our condition, so for the corresponding values in the average range, they are excluded in calculation.

{60;55;62;90;83;58;88;74;90} -> {60;0;62;90;83;0;88;74;90} – Ignore blank cells

Now this new array only contains numbers. We can calculate the average now (60+62+90+83+88+74+90)/7=78.14.

SUMMARY

1. AVERAGE function is used for returning the average of some numbers in Excel.

2. AVERAGEIFS function returns the average of some numbers refer to one or more given condition or criteria.

3. AVERAGEIFS can support wildcards, logical expressions.

Related Functions


  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Excel AVERAGEIFS function
    The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

 

 

Sidebar