How to exclude empty/non-zero cells in average?

Copper Contributor

I'm trying to find the average of every 16th column in a row and want it to not include empty cells and ones that have NA entered. I've been using this formula: =AVERAGE(IF((MOD(COLUMN(K14:AIG14),16)=11)*(K14:AIG14<>""),K14:AIG14)) however, this also excludes 0s from my average which is something I don't want. I tried doing >=0 instead of <>"' but this ends up setting NA and empty cells equal to 0 and including those in the calculation. Does anyone know how to avoid/fix this?

1 Reply


=AVERAGE(IF((MOD(COLUMN(K14:AIG14),16)=11)*(K14:AIG14<>""),K14:AIG14)) will exclude text values and empty cells, but it will include cells with value 0.

Here is a simple example:


The formula calculates the average of the non-blank cells in even-numbered columns in B2:K2.

There are three such cells: B2=2, D2=0, and H2=7. Their average is (2+0+7)/3 = 9/3 = 3.