Excel beginner average conditional formula

How to Calculate Average If Not Blank in Excel

Average If Conditional is a Excel function that calculates the average of values in column b where corresponding cells in column a are not blank.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

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.

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

  1. A:A specifies the range to check for non-blank cells.
  2. "<>" is the criteria indicating that the cell should not be blank.
  3. B:B specifies the range of values to average.
  4. 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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples