How to make a table include new columns in a row average?

[JenniferMurphy]

The table below calculates the composite rankings of the teams in the NFC playoffs based on the averages of several different rankings. My formula in the Ave column uses literal column addresses. If I find another ranking (Rtg4) and add a new column, I have to edit the formula.

Tables.xlsx
	C	D	E	F	G	H
5	Team	Rank	Ave	Rtg1	Rtg2	Rtg3
6	49ers	1	1.0000	1	1	1
7	Lions	2	2.6667	2	2	4
8	Eagles	3	3.3333	4	4	2
9	Packers	4	4.0000	3	6	3
10	Buccaneers	5	4.6667	6	3	5
11	Rams	6	5.3333	5	5	6
12	Cowboys	7	7.0000	7	7	7
Expandable Col Aves

Cell Formulas
Range	Formula
D6:D12	D6	=RANK.EQ([@Ave],[Ave],1)
E6:E12	E6	=AVERAGE([@Rtg1]:[@Rtg3])

In this table, I added a dummy last column so I could use Offset for an average formula that automatically adjusts for any new columns. Is there a better way that does not need a dummy column?

Tables.xlsx
	B	C	D	E	F	G	H	I	J
5	Team	Rank	Ave1	Ave2	Rtg1	Rtg2	Rtg3	Rtg4	xxx
6	49ers	1	1.0000	1.0000	1	1	1	1
7	Lions	2	2.6667	2.7500	2	2	4	3
8	Eagles	3	3.3333	4.0000	4	4	2	6
9	Packers	4	4.0000	3.5000	3	6	3	2
10	Buccaneers	5	4.6667	4.5000	6	3	5	4
11	Rams	6	5.3333	5.2500	5	5	6	5
12	Cowboys	7	7.0000	7.0000	7	7	7	7
Expandable Col Aves

Cell Formulas
Range	Formula
C6:C12	C6	=RANK.EQ([@Ave1],[Ave1],1)
D6:D12	D6	=AVERAGE([@Rtg1]:[@Rtg3])
E6:E12	E6	=AVERAGE(OFFSET([@Ave2],0,1):OFFSET([@xxx],0,-1))

Thanks

 

[Fluff]

How about

=AVERAGE(DROP(Table1[@],,XMATCH(Table1[[#Headers],[Ave]],Table1[#Headers])))

Change the table name to suit.

 

[JenniferMurphy]

How about

=AVERAGE(DROP(Table1[@],,XMATCH(Table1[[#Headers],[Ave]],Table1[#Headers])))

Change the table name to suit.

Wow!!! Elegant and effective. Very nice. Thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JenniferMurphy]

The Drop function in your example drops the first 4 columns from the table. Is there a slightly different formula that drops the first n and the last m columns in a table? Or is there an Include or Keep function that will keep a variable number of columns in the middle of a table?

 

[Fluff]

You can replace the xmatch function with a the number of columns you want to drop from the start of the table & wrap it in another DROP where you can give it a -ve number to drop the last m columns.

 

[RoryA]

If all your rating columns start with Rtg couldn't you just use:

=AVERAGEIF(Table1[#Headers],"Rtg*",Table1[@])