Average excluding #div/0 in non-range

[micsarge]

I've seen a lot of good formulas for averages when dealing with numbers in a range. Unfortunately my values are in every other column. What I am trying to do is calculate =AVERAGE(Y6,W6,U6,S6,Q6,O6,M6,K6,I6,G6,E6,C6) excluding any #div/0. Any recommendations?

 

[NBVC]

Try:

=AVERAGE(IF((MOD(COLUMN(C6:Y6)-COLUMN(C6),2)=0)*ISNUMBER(C6:Y6),C6:Y6))

confirmed with CTRL+SHIFT+ENTER not just ENTER

 

[SteveO59L]

Why not address the #div/0 errors, rather than having an extended formula which might become a "housekeeping" problem later

 

[micsarge]

NBVC, that worked perfectly. I hope you will review this thread again and respond to my question below.

Since I'm obviously a novice at excel formulas, I'd like to understand the logic behind what you wrote. What exactly is the formula telling excel to do.

Thanks!

Michael

 

[NBVC]

=AVERAGE(IF((MOD(COLUMN(C6:Y6)-COLUMN(C6),2)=0)*ISNUMBER(C6:Y6),C6:Y6))

I am not the best at explanations, but here it goes.....


The MOD(number,divisor) function finds the remainder of a number divided by a divisor.

In the formula above...it divides the column numbers between column C and column Y (with column C becoming column #0 via the COLUMN(C6:Y6)-COLUMN(C6) part of the formula), by the divisor, 2 (which, in this case is your N value - i.e every 2nd column).

The MOD(COLUMN(C6:Y6)-COLUMN(C6),2) function churns out an array like {0,1,0,1,0,1,0,1...etc).

The IF() condition says if the Mod() result=0 then TRUE. The IF() condition further checks that the value in row 6 is actually numeric (no text, errors). If both conditions are TRUE, then the ultimate result is TRUE

For every TRUE, the formula extracts the corresponding value from row 6 and finally it averages them all out.

Hopefully this cleared it up for you.

To see the steps of the formula in action, go to TOOLS|FORMULA AUDITING|EVALUATE FORMULA and continue clicking EVALUATE to each step of the evaluation. It should help you to understand how the formula works.