Averaging Elements of Non-Contiguous Columns in a Named Range

[studious]

• I have thousands of columns.
• I want to average over some of them.
• They are not adjacent.

I have created named ranges using concatenate because the column headers have an easy-to-follow structure. So, I have narrowed the column combinations to 19 named ranges, which contain the columns that I need and all rows. I am trying to average the values of the columns for each row like so:

AVERAGE(INDEX(HORRIBLE_RANGE_1,2,))

What I thought this means is, average the second row of HORRIBLE_RANGE_1. But, I think what it does is to just give me the very first value instead of the average of all the values for the columns in that HORRIBLE_RANGE_1.

I have seen suggestions with wretched formulas the length of newspaper articles.

Can anyone offer an elegant solution?

Thank you!

 

[CalcSux78]

If you're using NamedRanges, you should be able to use the Average Worksheet function like this:

Excel 2010
	B	C	D	E	F
2	10		5		5.6
3	10		3
4	2		6
5	7		8
6	10		4
7	2		9
8	4		5
9	9		1
10	7		6
11	3		1
Sheet1

Cell Formulas
Range	Formula
F2	=AVERAGE(Range1,Range2)

Named Ranges
Name	Refers To	Cells
Range1	=Sheet1!$B$2:$B$11
Range2	=Sheet1!$D$2:$D$11

 

[studious]

Thanks for the reply. Maybe I didn't explain it well, but here it is. My named range contains, say columns B D F and I would like to get the average for B D F for each row. In reality it is more tedious because I have literally a thousand columns, which I have neatly covered in 19 named ranges. However, I can't seem to use the named ranges to do what I aim to.

 

[Eric W]

Excel really does not handle multi-area selections well. Some functions will handle the whole range OK "SUM(Horrible_Range_1)" and some, like INDEX, make an attempt to reference the different areas, but overall it just doesn't work well. I tried for a bit to come up with something but came up short.

However, I did create a UDF (User-Defined Function) that does the ticket, and it's pretty simple.

1) Open your workbook.
2) Right click on the sheet tab on the bottom and select View Code.
3) From the VBA menu, click Insert > Module.
4) Paste the following code into the window that opens:

Public Function AvgRangeRow(ByRef rng As Range, ByVal r As Long) As Double

    AvgRangeRow = WorksheetFunction.Average(Intersect(rng, ActiveSheet.Rows(r)))
    
End Function

5) Press Alt-Q to close the editor.

Now just enter your function like this:

=AvgRangeRow(Horrible_Range_1,2)

Your ranges and formulas should be on the same sheet.

It's just a 1-line UDF, pretty simple. If the Intersect command were available in native Excel, a native formula would be easy. Hope this helps.

 

[studious]

Thanks, Eric. That's genius!

I've been banging my head over this for two days and nothing worked. I was just in the process of replacing my formulas with rather large AVERAGEIFS.

Never imagined it would be so hard. I am incredibly grateful for your solution, but I think I am staying away from multi-area selections from now on.

 

[Eric W]

I think I am staying away from multi-area selections from now on.

Probably a good idea!

Glad I could help.