# Ignore Blank Cells

1. ## Ignore Blank Cells

Hi all,

I'm creating a spreadsheet for work and it will basically consist of a list of percentages followed by an average.

I tried using the =AVERAGE function BUT it returned DIV/0! errors. To combat that I used the formula +IF((COUNT(B6:B13)>0),AVERAGE(B6:B13),"") so that the average is blank if the cells are empty.

Only problem with that is that the COUNT function is returning a "Refers to empty cells error" on every formula cell. I could just turn off the error checking but because this is used on loads of computers and potentially ones that I don't have access to I can't do that for all of them.

Anyone got any other suggestions?

Thanks,

Kevin  Register To Reply

2. How about =SUM(B6:B13)/COUNTIF(B6:B13,">0") or =IF(COUNTIF(B6:B13,">0")=0,0,SUM(B6:B13)/COUNTIF(B6:B13,">0")

Bozo  Register To Reply

3. Unfortunately, they both create the same error.

Thanks anyway,

Kevin.  Register To Reply

if(sum(b6:b13)>0,average(b6:b13),"")

meaning:
"if the sum of the range is bigger than 0, then give the average, else give a blank."  Register To Reply