Forum Discussion
How to exclude empty/non-zero cells in average?
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)...
=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.