# Ignore blank cells in a average

This is a discussion on Ignore blank cells in a average within the Excel Questions forums, part of the Question Forums category; All, I am doing an average of a column of cells. How do I tell excel to ignore the blank ...

1. ## Ignore blank cells in a average

All,

I am doing an average of a column of cells. How do I tell excel to ignore the blank cells in the column and not calculate them in the average?

G.

2. ## Re: Ignore blank cells in a average

If the cells are truly blank the Average() formula will overlook them.

3. Hi

Welcom to the board.

Excel does this automatically. Just use =average(A:A) or something like that and blank cells will be ignored automatically.

4. ## Re: Ignore blank cells in a average

The AVERAGE function will ignore blank cells, so it shouldn't be an issue (unless your cell is not, in fact, blank, but has a zero in it).

5. If the cells are not blank and you only want values greater that zero calculated use something like this

=SUM(A:A)/COUNTIF(A:A,">0")

modify as needed.

Jacob

6. ## Re: Ignore blank cells in a average

Actually, if negatives are a possibility and you want to ignore zeroes, you would want to modify DRJ's response a little:

=SUM(A:A)/COUNTIF(A:A,"<>0")

Otherwise a negative value could hose the average since it would be included in the SUM portion, but not in the COUNTIF portion.

7. Or:

=AVERAGE(IF(N22:N28<>0,N22:N28))

Confirm using ctrl, shift, enter

8. ## Re: Ignore blank cells in a average

Originally Posted by jmiskey
Actually, if negatives are a possibility and you want to ignore zeroes, you would want to modify DRJ's response a little:

=SUM(A:A)/COUNTIF(A:A,"<>0")

Otherwise a negative value could hose the average since it would be included in the SUM portion, but not in the COUNTIF portion.
Try it on the following sample

{3;4;2;"";"x";0;0;" ";0}

that A2:A10 houses. A4 houses the formula

=IF(\$B\$1,1,"")

where B1 is a unused, empty cell. Note that A9 houses a space.

9. ## Re: Ignore blank cells in a average

Your right Aladdin (of course), the COUNTIF will count blank spaces and text and hose your formula.

I guess the \$64,000 question is: what does your data look like? Do you want to include zeroes in the average?

10. ## Re: Ignore blank cells in a average

Originally Posted by Greg Truby
If the cells are truly blank the Average() formula will overlook them.
That is not true if all cells are blank in range.

I have a range of cells used for Status averages. Initially each week all cells are blank and then filled in as tasks are completed or partially.
I want an average of all non-blank cells at the top but using any of the formulas given here, there is an error when all cells are blank. Any input?

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•