# Formula for different averages in a table

#### Brutor

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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### DRSteele

##### Well-known Member
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.

23.193.484.002.50

<tbody>
</tbody>
Sheet10

Worksheet Formulas
CellFormula
AC2=AVERAGE(H2:AB2)
AE2=IFERROR(AVERAGEIFS(\$H2:\$AB2,\$H\$1:\$AB\$1,E2),"")

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
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>

Last edited:

#### Brutor

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

##### Well-known Member
Super. You're welcome.

Replies
3
Views
40
Replies
8
Views
175
Replies
5
Views
116
Replies
2
Views
234
Replies
3
Views
108

1,127,556
Messages
5,625,495
Members
416,111
Latest member
User81

### 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