Formula-1 race grid positions given the qualifying results and the various grid penalties

I'm trying (so far unsuccessfully) to produce a sheet that works out the Formula-1 race grid positions given the qualifying results and the various grid penalties imposed by the FIA.

For example, at race 12 in Belgium (the race that never was):
Qualifying order:
1 VER
2 RUS
3 HAM
4 RIC
5 VET
6 GAS
7 PER
8 BOT 5 place penalty
9 OCO
10 NOR 5 place penalty
11 LEC
12 LAT
13 SAI
14 ALO
15 STR 5 place penalty
16 GIO
17 TSU
18 MSC
19 RAI Pit-lane start
20 MAZ

Grid order:
1 VER
2 RUS
3 HAM
4 RIC
5 VET
6 GAS
7 PER
8 OCO
9 LEC
10 LAT
11 SAI
12 ALO
13 BOT
14 GIO
15 NOR
16 TSU
17 MSC
18 MAZ
19 STR
20 RAI

Here's another example, from race 15 in Russia:
Qualifying order:
1 NOR
2 SAI
3 RUS
4 HAM
5 RIC
6 ALO
7 BOT 15 place penalty
8 STR
9 PER
10 OCO
11 VET
12 GAS
13 TSU
14 LAT back of grid penalty
15 LEC back of grid penalty
16 RAI
17 MSC
18 GIO 5 place penalty
19 MAZ
20 VER 3 place penalty + back of grid penalty

Grid order:
1 NOR
2 SAI
3 RUS
4 HAM
5 RIC
6 ALO
7 STR
8 PER
9 OCO
10 VET
11 GAS
12 TSU
13 RAI
14 MSC
15 MAZ
16 BOT
17 GIO
18 LAT
19 LEC
20 VER

I've used the "pit-board" abbreviations for the drivers in the lists above, so VER for Verstappen and HAM for Hamilton etc.

So - just adding the penalties to the qualifying order and then shuffling everyone else up does not work. I've tried various methods and can't get a consistent result.

Does anyone have any ideas?

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. I have done it for you as a courtesy this time.)


There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

Abd your Excel version is the very old Excel 2003?

Why is that a problem?

Just checking, because you failed to respond to the final part of my first post in this thread.

It will only be a problem in so far as (a) most members will have moved on and therefore (b) will not have your version to test their solutions on.

Oh, and (c) it means we can't use more up-to-date functions that exist in newer versions of Excel.

Thanks for letting us know.