# Average excluding #div/0 in non-range

#### micsarge

##### New Member
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?

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Try:

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

confirmed with CTRL+SHIFT+ENTER not just ENTER

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

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

=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.

Replies
5
Views
163
Replies
22
Views
546
Replies
8
Views
231
Replies
2
Views
316
Replies
3
Views
367

1,221,007
Messages
6,157,350
Members
451,417
Latest member
Ilu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back