Formula for different averages in a table

[Brutor]

Hello MrExcel experts!

I have been working on my bachelor's thesis for a while (I'm investigating whether or not there is a home advantage in Formula One) and ran into some problems earlier today.
Below I have posted the 2016 season, with all cells filled in by hand (numbers are final position in the race). My question concerns the columns in bold, at the end of the table.
Right now all the values are entered by hand, but I am looking for three formulas that can save me a great deal of time, since I have about fifty more seasons to go.
About the coding: The R stands for Race (I use a similar table for qualification), the D stands for Driver, and the C stands for Constructor. The R D AVG column is easy enough, a simple average of all races.

I have trouble coming up with a formula for R C AVG. Here I would like Excel to print the average finishing position of all drivers of the same constructor, combined. Usually there are two, but sometimes three or four.

For the next column, R D Home, I would like to get the finishing position of a driver in his home race. E.g. Nico Rosberg's D HOME = GER, and his final position in GER was 4. Some drivers do not have a home race, so a null value for those is alright.

Finally, and I suppose the most difficult one, in the R C HOME column, I would like Excel to give me the average finishing position of all drivers racing for the same constructor, at the home race of the constructor. E.g. Neither Vettel or Raikkonen are Italian, but they race for an Italian constructor. Therefore I would like the average of their finishing positions printed in the R C HOME column.​

I have been playing around with VLOOKUP, MATCH, and IF commands for a couple of hours now, but I cannot seem to get it done. I really hope there is someone on these boards who
can help me out with this. To everyone still reading, thank you very much!

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	DRIVER	POS	QUALITY	SEASON	D HOME	CONSTRUCTOR	C HOME	AUS	BAH	CHI	RUS	SPA	MON	CAN	EUR	AUT	BRI	HUN	GER	BEL	ITA	SIN	MAL	***	USA	MEX	BRA	ABU	R D AVG	R C AVG	R D HOME	R C HOME
2	Nico Rosberg	1	Front	2016	GER	Mercedes	GER	1	1	1	1	22	7	5	1	4	3	2	4	1	1	1	3	1	2	2	2	2	3.19	3.48	4	2.5
3	Lewis Hamilton	2	Front	2016	BRI	Mercedes	GER	2	3	7	2	21	1	1	5	1	1	1	1	3	2	3	18	3	1	1	1	1	3.76	3.48	1	2.5
4	Daniel Ricciardo	3	Front	2016	AUS	Red Bull	AUT	4	4	4	11	4	2	7	7	5	4	3	2	2	5	2	1	6	3	3	8	5	4.38	5.64	4	3.5
5	Sebastian Vettel	4	Front	2016	GER	Ferrari	ITA	3	21	2	20	3	4	2	2	21	9	4	5	6	3	5	22	4	4	5	5	3	7.29	7.40	5	3.5
6	Max Verstappen	5	Front	2016	NED	Red Bull	AUT	10	6	8	19	1	18	4	8	2	2	5	3	11	7	6	2	2	20	4	3	4	6.90	5.64		3.5
7	Kimi Räikkönen	6	Front	2016	FIN	Ferrari	ITA	18	2	5	3	2	21	6	4	3	5	6	6	9	4	4	4	5	19	6	20	6	7.52	7.40		3.5
8	Sergio Pérez	7	Front	2016	MEX	Force India	IND	13	16	11	9	7	3	10	3	17	6	11	10	5	8	8	6	7	8	10	4	8	8.57	9.95	10
9	Valtteri Bottas	8	Front	2016	FIN	Williams	BRI	8	9	10	4	5	12	3	6	9	14	9	9	8	6	20	5	10	16	8	11	21	9.67	10.52		12.5
10	Nico Hulkenberg	9	Mid	2016	GER	Force India	IND	7	15	15	21	20	6	8	9	19	7	10	7	4	10	21	8	8	22	7	7	7	11.33	9.95	7
11	Fernando Alonso	10	Mid	2016	SPA	McLaren	BRI	21	-	12	6	19	5	11	19	18	13	7	12	7	14	7	7	16	5	13	10	10	11.60	11.83	19	12.5
12	Felipe Massa	11	Mid	2016	BRA	Williams	BRI	5	8	6	5	8	10	20	10	20	11	18	22	10	9	12	13	9	7	9	18	9	11.38	10.52	18	12.5
13	Carlos Sainz	12	Mid	2016	SPA	Toro Rosso	ITA	9	18	9	12	6	8	9	21	8	8	8	14	20	15	14	11	17	6	16	6	18	12.05	13.26	6	17.0
14	Romain Grosjean	13	Mid	2016	FRA	Haas	USA	6	5	19	8	18	13	14	13	7	20	14	13	13	11	22	21	11	10	20	22	11	13.86	14.45		15.5
15	Daniil Kvyat	14	Mid	2016	RUS	Toro Rosso	ITA	22	7	3	15	10	20	12	22	22	10	16	15	14	19	9	14	13	11	18	13	19	14.48	13.26	15	17.0
16	Jenson Button	15	Mid	2016	BRI	McLaren	BRI	14	20	13	10	9	9	22	11	6	12	22	8	21	12	19	9	18	9	12	16	20	13.90	11.83	12	12.5
17	Kevin Magnussen	16	Mid	2016	DEN	Renault	FRA	12	11	17	7	15	19	16	14	14	17	15	16	18	17	10	20	14	12	17	14	22	15.10	15.55		-
18	Felipe Nasr	17	Rear	2016	BRA	Sauber	SWI	15	14	20	16	14	17	18	12	13	15	17	21	17	22	13	17	19	15	15	9	16	15.95	15.90	9	-
19	Jolyon Palmer	18	Rear	2016	BRI	Renault	FRA	11	22	22	13	13	22	21	15	12	18	12	19	15	21	15	10	12	13	14	19	17	16.00	15.55	18	-
20	Pascal Wehrlein	19	Rear	2016	GER	MRT	BRI	16	13	18	18	16	14	17	20	10	22	19	17	22	20	16	15	22	17	22	15	14	17.29	17.65	17	20.5
21	Stoffel Vandoorne	20	Rear	2016	BEL	McLaren	BRI	-	10	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	10.00	11.83
22	Esteban Gutierrez	21	Rear	2016	MEX	Haas	USA	20	19	14	17	11	11	13	16	11	16	13	11	12	13	11	19	20	21	19	17	12	15.05	14.45	19	15.5
23	Marcus Ericsson	22	Rear	2016	SWE	Sauber	SWI	17	12	16	14	12	16	15	17	15	21	20	18	19	16	17	12	15	14	11	21	15	15.86	15.90		-
24	Esteban Ocon	23	Rear	2016	FRA	MRT	BRI	-	-	-	-	-	-	-	-	-	-	-	-	16	18	18	16	21	18	21	12	13	17.00	17.65		-
25	Rio Haryanto	24	Rear	2016	***	MRT	BRI	19	17	21	22	17	15	19	18	16	19	21	20	-	-	-	-	-	-	-	-	-	18.67	17.65		20.5

<tbody>
</tbody>

 

[DRSteele]

Welcome to the forum.

This should do it. It is imperative you remove all the - in the empty cells where a racer did not participate in the race; just delete the contents of each of them. Also, the three * caused a headache because * is a wildcard text string in Excel; change the two occurrences to the code for Japan.

Enter these formulas in Row2, paying attention to the Ctrl+Shift+Enter method of invoking AF2. Copy downwards to Row25.

	AC	AD	AE	AF
2	3.19	3.48	4.00	2.50

<tbody>
</tbody>

Sheet10​

Worksheet Formulas Cell Formula AC2 =AVERAGE(H2:AB2) AD2 =AVERAGEIFS($AG$2:$AG$25,$F$2:$F$25,F2) AE2 =IFERROR(AVERAGEIFS($H2:$AB2,$H$1:$AB$1,E2),"") <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula AF2 {=IFERROR(SUM(IFERROR((($F2=$F$2:$F$25)*($G2=$G$2:$G$25)*($G2=H$1:AB$1))*$H$2:$AB$25,0))/SUM(IF((F2=$F$2:$F$25)*(G2=$H$1:$AB$1)*$H$2:$AB$25>0,1,0)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

 

[Brutor]

Thank you so much, DRSteele, for taking the time to write all that down. You just easily saved me three days of work. You rock! :D

 

[DRSteele]

Super. You're welcome.