Average excluding #div/0 in non-range

micsarge

New Member
Joined
Sep 15, 2004
Messages
17
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
Why not address the #div/0 errors, rather than having an extended formula which might become a "housekeeping" problem later
 

micsarge

New Member
Joined
Sep 15, 2004
Messages
17
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
=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.
 

Forum statistics

Threads
1,136,267
Messages
5,674,729
Members
419,523
Latest member
Urnovio

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top