Ignore blank cells in a average

g29115

New Member
Joined
Oct 30, 2003
Messages
3
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
jmiskey said:
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top