Excel formula: Average if criteria not blank

Generic formula

=AVERAGEIFS(range1,range2,"<>")

Explanation

To calculate an average, excluding blank values in a criteria range, you can use the AVERAGEIFS function and a simple logical expression. In the example shown, the formula in F6 is:

=AVERAGEIFS(price,group,"<>")

where "price" (C5:C15) and "group" (D5:D15) are named ranges.

How this formula works

Excel supports a number of logical operators that can be used in various ways to construct criteria. In this example, we are using the not equals to operator (<>) to exclude values with no group in D5:D15. The range and criteria are supplied as two arguments:

D5:D15,"<>"

Which is equivalent to the expression:

D5:D15<>""

which means not equal to an empty string, e.g. not empty.

Excluding formulas

The formula above will not exclude empty strings returned by formulas (=""). If you have empty strings returned by formulas in the criteria range, you can adjust the criteria like this:

=AVERAGEIFS(C5:C15,D5:D15,">""")

This will correctly exclude empty string returned by formulas.

Author

Dave Bruns

Related formulas

Average numbers ignore zero

To get the average of a set of numbers, excluding or ignoring zero values, use the AVERAGEIF function. In the example shown, the formula in E6 is: = AVERAGEIF ( B6:D6 , "<>0" ) How this formula works In the example shown, the formula...

Average top 3 scores

To average the top 3 scores in a data set, you can use a formula based on the LARGE function. In the example shown, the formula in G6 is: = AVERAGE ( LARGE ( B6:F6 , { 1 , 2 , 3 } )) How this formula works The LARGE function can retrieve the top nth...

Average last 5 values

To average the last 5 data points, you can use the AVERAGE function together with the COUNT and OFFSET functions. You can use this approach to average the last N data points: last 3 days, last 6 measurements, etc. In the example shown, the formula...

Related functions

Excel AVERAGEIFS Function

The Excel AVERAGEIFS function calculates the average of numbers in a range that meet one or more supplied criteria. The criteria in AVERAGEIFS supports logical operators (>,,=) and wildcards (*,?) for partial matching....

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.