How to NOT take an average if any blank cells

**gjames21** · 11-06-2014, 07:33 AM

I am aware that the Average function does not include blank cells in the calculation, this is not my question.

I would like to take an average of a row of 4 cells and copy the formula down a LARGE spreadsheet. There are some rows were only 1 or 2 out of the 4 contain any values and I would NOT like to return an average for any of these rows.

Please help me all you Excel geniuses out there.
Many thanks

**twiggywales** · 11-06-2014, 07:45 AM

i guess you would use something like this

=IF(COUNTA(A1:D1)<>4,AVERAGE(A1:D1),"")

**Glenn Kennedy** · 11-06-2014, 07:45 AM

=IF(COUNT(A1:D1)=4,AVERAGE(A1:D1),"")

will only average if there are numbers in each of the four cells. Copy down as needed.

**gjames21** · 11-06-2014, 07:51 AM

Thank you both! Works perfectly with =4. I did not know about the COUNTA function so you have both helped enormously :D

**Pete_UK** · 11-06-2014, 07:55 AM

COUNTA will count text values (or spaces), which will give you errors, so you might be better using COUNT in this instance.

Pete

**gjames21** · 11-06-2014, 09:10 AM

Great, thanks for the extra clarity Pete

**worthm** · 11-06-2014, 07:06 PM

This works well with rows or columns of cells with zeroes that you don't want to count

=AverageIF(B1:K1, "<>0")
I've attached a picture (though I don't know if you can see it) of my sheet with the equation displayed. Hope this helps