Average if Not Blank in Excel: A Complete Guide
This article explains how to calculate the average of a range in Excel while ignoring blank cells, a common task when working with incomplete datasets. You’ll learn the correct formulas, how to handle different scenarios, and common pitfalls to avoid. Whether you’re a beginner or an experienced user, this guide provides clear, step-by-step instructions.
Understanding the AVERAGE Function
The standard AVERAGE function in Excel already ignores blank cells by default. For example, =AVERAGE(A1:A10) will calculate the mean of all numeric values in that range, skipping any empty cells. However, problems arise when you have cells that look blank but actually contain formulas returning empty strings (“”), or when you need to average based on a condition. In these cases, you need alternative approaches.
Using AVERAGEIF to Ignore Blanks
The AVERAGEIF function is the most straightforward way to average cells that meet a specific criterion, including ignoring blanks. The syntax is:
=AVERAGEIF(range, criteria, [average_range])
To average a range while ignoring blanks, you can use:
=AVERAGEIF(A1:A10, "<>")
This formula tells Excel to average only cells that are not empty. The criteria "<>" means “not equal to nothing.” This works perfectly for truly blank cells, but it will also include cells containing text or errors. If you want to ignore both blanks and text, you can use ">0" or "<>0" depending on your data, but this only works if all your values are positive or negative respectively.
Handling Cells with Formulas Returning Empty Strings
When a formula in a cell returns an empty string (“”), the cell appears blank but is not truly empty. The AVERAGEIF function with "<>" will still include these cells in the calculation, treating the empty string as text, which can lead to errors or incorrect results. To ignore these, you need a more robust formula using SUMPRODUCT or an array formula.
One common solution is:
=SUMPRODUCT((A1:A10<>"")*(A1:A10))/SUMPRODUCT(--(A1:A10<>""))
This formula counts only cells that are not empty strings and averages them. It works by multiplying the values by a boolean array (TRUE/FALSE) that is converted to 1s and 0s. This method ignores both truly blank cells and cells with empty strings.
Using AVERAGEIFS for Multiple Conditions
If you need to average cells based on multiple criteria, AVERAGEIFS is the function to use. For example, to average values in column B where column A is “Product A” and column B is not blank, you would write:
=AVERAGEIFS(B1:B10, A1:A10, "Product A", B1:B10, "<>")
This is particularly useful when you have a dataset with categories and you want to calculate the average for each category while ignoring blanks.
Common Mistakes and How to Avoid Them
One common mistake is using AVERAGEIF with "<>" when your data contains text entries. For instance, if your range includes the word “N/A”, the formula will include it and return a #DIV/0! error. To avoid this, use "<>"&"" or better yet, use SUMPRODUCT as shown above. Another mistake is forgetting that AVERAGEIF ignores cells with text in the average_range but not in the range when using "<>". Always double-check your criteria and test with sample data.
Alternatives: Using AGGREGATE or Array Formulas
For Excel 2010 and later, the AGGREGATE function offers a powerful alternative. The formula =AGGREGATE(1, 6, A1:A10) calculates the average (function 1) while ignoring errors (option 6) and blanks. This is particularly useful when your range contains errors like #DIV/0! that you want to ignore.
Array formulas, entered with Ctrl+Shift+Enter in older Excel versions, can also be used. For example:
=AVERAGE(IF(ISNUMBER(A1:A10), A1:A10))
This formula checks if each cell is a number and averages only those. In modern Excel (365 and 2021), this works as a dynamic array formula without needing Ctrl+Shift+Enter.
Frequently Asked Questions
Why does AVERAGEIF with “<>” still include blank-looking cells?
Because those cells may contain formulas that return empty strings (“”). The "<>" criterion treats any cell that is not truly empty as non-blank, including cells with empty strings. To exclude them, use SUMPRODUCT or AGGREGATE as described above.
How do I average only non-blank cells in a column with text and numbers?
Use =AVERAGEIF(A:A, "<>") if you want to include text (which will be ignored) and numbers, but this will include text cells in the count, potentially causing errors. Better to use =AVERAGE(IF(ISNUMBER(A:A), A:A)) as an array formula, which averages only numeric cells.
Can I ignore blanks when averaging across multiple sheets?
Yes, but you need to use a 3D reference or combine ranges. For example, =AVERAGE(Sheet1:Sheet3!A1:A10) will average across all sheets, ignoring blanks. However, this includes all cells, even if blank. To ignore blanks across sheets, you may need a more complex formula or use the CONSOLIDATE feature.
What is the difference between AVERAGE and AVERAGEA?
AVERAGE ignores text and logical values (TRUE/FALSE) in the range, while AVERAGEA includes them, treating TRUE as 1 and FALSE as 0. For ignoring blanks, both functions ignore truly blank cells, but AVERAGEA will include cells with empty strings as text, which can cause errors.
How do I average the top 5 non-blank values in a range?
Use =AVERAGE(LARGE(IF(A1:A100<>"", A1:A100), {1,2,3,4,5})) as an array formula. This filters out blanks, takes the top 5 values, and averages them. In Excel 365, you can use =AVERAGE(TAKE(SORT(FILTER(A1:A100, A1:A100<>""), -1), 5)).