Formula for different averages in a table

Brutor

New Member
Joined
Apr 24, 2017
Messages
2
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!



ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1DRIVERPOSQUALITYSEASOND HOMECONSTRUCTORC HOMEAUSBAHCHIRUSSPAMONCANEURAUTBRIHUNGERBELITASINMAL***USAMEXBRAABUR D AVGR C AVGR D HOMER C HOME
2Nico Rosberg1Front2016GERMercedesGER11112275143241113122223.193.4842.5
3Lewis Hamilton2Front2016BRIMercedesGER237221115111132318311113.763.4812.5
4Daniel Ricciardo3Front2016AUSRed BullAUT44411427754322521633854.385.6443.5
5Sebastian Vettel4Front2016GERFerrariITA32122034222194563522445537.297.4053.5
6Max Verstappen5Front2016NEDRed BullAUT106819118482253117622204346.905.64
3.5
7Kimi Räikkönen6Front2016FINFerrariITA18253221643566944451962067.527.40
3.5
8Sergio Pérez7Front2016MEXForce IndiaIND131611973103176111058867810488.579.9510
9Valtteri Bottas8Front2016FINWilliamsBRI891045123691499862051016811219.6710.52
12.5
10Nico Hulkenberg9Mid2016GERForce IndiaIND71515212068919710741021882277711.339.957
11Fernando Alonso10Mid2016SPAMcLarenBRI21-126195111918137127147716513101011.6011.831912.5
12Felipe Massa11Mid2016BRAWilliamsBRI5865810201020111822109121397918911.3810.521812.5
13Carlos Sainz12Mid2016SPAToro RossoITA9189126892188814201514111761661812.0513.26617.0
14Romain Grosjean13Mid2016FRAHaasUSA6519818131413720141313112221111020221113.8614.45
15.5
15Daniil Kvyat14Mid2016RUSToro RossoITA22731510201222221016151419914131118131914.4813.261517.0
16Jenson Button15Mid2016BRIMcLarenBRI14201310992211612228211219918912162013.9011.831212.5
17Kevin Magnussen16Mid2016DENRenaultFRA1211177151916141417151618171020141217142215.1015.55
-
18Felipe Nasr17Rear2016BRASauberSWI1514201614171812131517211722131719151591615.9515.909-
19Jolyon Palmer18Rear2016BRIRenaultFRA11222213132221151218121915211510121314191716.0015.5518-
20Pascal Wehrlein19Rear2016GERMRTBRI16131818161417201022191722201615221722151417.2917.651720.5
21Stoffel Vandoorne20Rear2016BELMcLarenBRI-10-------------------10.0011.83

22Esteban Gutierrez21Rear2016MEXHaasUSA20191417111113161116131112131119202119171215.0514.451915.5
23Marcus Ericsson22Rear2016SWESauberSWI17121614121615171521201819161712151411211515.8615.90
-
24Esteban Ocon23Rear2016FRAMRTBRI------------16181816211821121317.0017.65
-
25Rio Haryanto24Rear2016***MRTBRI191721221715191816192120---------18.6717.65
20.5


<tbody>
</tbody>
 

Some videos you may like

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
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
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.

ACADAEAF
23.193.484.002.50

<tbody>
</tbody>
Sheet10

Worksheet Formulas
CellFormula
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
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
Joined
Apr 24, 2017
Messages
2
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
Joined
Mar 31, 2015
Messages
2,354
Office Version
  1. 365
Platform
  1. Windows
Super. You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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