Motocross Racing position formula or code

nhk26

New Member
Joined
Mar 23, 2016
Messages
2
Hi,

I am trying to create some type of code or formula to assist in creating final positions for riders in motocross. We have previously been doing this manually (handwritten) by griding laps and placing by riders number. Someone would call numbers out as they go past and watch 1st, 2nd or 3rd place and then when they come around again would move to the next lap.

We would then review at the end of the day and add races together based on rider placing/race. There could be several races/day. See simple example below
1st2nd 3rd4th5th
Lap 1111222333444555
Lap 2222333111555444
Lap 3222333444555111
Lap 4333222555444111

<tbody>
</tbody>

Each placing has a points value as per below
PlacingPoints
125
222
320
418
516
615
714
813
912
1011
1110
129
138
147
156
165
174
183
192
201

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
Didn't finish DNF

This works 95% of the time however with a large number of riders this can get very confusing due to the front runners lapping people and or falling off, you can get mixed up and it takes a long time to sort it out at the end of the day. I would also like to automate it as much as possible and use excel to calculate and work out all the fields.

I would like to simplify it by typing in the riders number in excel in a column. The code would then look up the column, count the maximum times a riders number appears, this would be the amount of maximum laps completed and then the last time it occurs return the place/position it occurs in the list. I would then like it to return the position and if possible points allocated for that position in a separate cell. Below is a simplified example.

Bike numbersPlacingPoints
111
222
333
444
555
222
333
111
555
444
222
333
444
555
111
333125
222223
555320
444418
111516

<tbody>
</tbody>

This shows the maximum amount of laps any rider did was 4 and 333 was 1st, 222 was 2nd, 555 was 3rd and so on.
A couple of things to consider is the maximum number of laps done might only be completed by a minimum of one rider who would obviously be first and also some riders may not finish. DNF

I am new to coding so any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
748
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi,
I’m not sure if I have understood your requirements correctly, but based on the idea that you type in column “A” the rider numbers as they cross the line, then maybe this will work for you.
It is based on a 4 lap race with 5 riders

Formula in cell B1
Code:
=MAX(B3:B22)

Formula in cell B3
Code:
=COUNTIF($A$3:$A3,A3)
And copied down

Formula in cell C3
Code:
=IF(B3=$B$1,RANK(B3,$B$3:$B$22)+COUNTIF(B3:$B$3,B3)-1,"-")
And copied down

Formula in cell C3
Code:
=SUMIF($M$3:$M$22,C3,$N$3:$N$22)
And copied down

The Placing/Points table is in the range M3:M22


Excel 2010
ABCD
1Total Laps = 4
2Bike #LapPlacingPoints
31111-0
42221-0
53331-0
64441-0
75551-0
81112-0
93332-0
104442-0
115552-0
121113-0
132222-0
142223-0
153333-0
161114125
174443-0
185553-0
193334222
202224320
215554418
224444516
23"Laps" uses Conditional Formatting
24Type in col "A" the order the Bikes cross the line

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1


Paul.

edit
The laps column can if required be colour coded with conditional formatting, I used this on my sheet but it doesn't show in the grid above.
 
Last edited:

nhk26

New Member
Joined
Mar 23, 2016
Messages
2
Thank you I believe this should work. I will give it a go this weekend.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

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