Excel beginner

How to Calculate Average If Not Blank in Excel

Struggling to find averages based on conditions? This guide shows you how to average values in one column based on non-blank criteria in another.

Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.

Generate this formula Browse library

The formula

Prompt

"Calculate the average of values in column B only where column A is not blank"

Excel

=AVERAGEIF(A:A, "<>", B:B)

This formula calculates the average of values in column B where corresponding cells in column A are not blank.

Step-by-step breakdown

A:A specifies the range to check for non-blank cells.
"<>" is the criteria indicating that the cell should not be blank.
B:B specifies the range of values to average.
AVERAGEIF function combines these elements to compute the average.

Edge cases & warnings

If all cells in column A are blank, the formula returns a #DIV/0! error.
If there are no corresponding values in column B, it will also return a #DIV/0! error.
Non-numeric values in column B will be ignored in the average calculation.

Examples

Prompt

"Column A: {1, , 3}, Column B: {10, 20, 30}"

Excel

Average is 20

Prompt

"Column A: { , 2, 3}, Column B: {5, 15, 25}"

Excel

Average is 20

Frequently asked questions

What happens if all cells in column A are blank?

The formula will return a #DIV/0! error.

Can I use this with non-contiguous ranges?

No, AVERAGEIF only works with contiguous ranges.

What if column B has text values?

Text values in column B will be ignored in the average calculation.

Can't find what you need?

Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.

Generate custom formula See examples