Motocross Racing position formula or code

[nhk26]

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

	1st	2nd	3rd	4th	5th
Lap 1	111	222	333	444	555
Lap 2	222	333	111	555	444
Lap 3	222	333	444	555	111
Lap 4	333	222	555	444	111

<tbody>
</tbody>

Each placing has a points value as per below

Placing	Points
1	25
2	22
3	20
4	18
5	16
6	15
7	14
8	13
9	12
10	11
11	10
12	9
13	8
14	7
15	6
16	5
17	4
18	3
19	2
20	1

<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 numbers	Placing	Points
111
222
333
444
555
222
333
111
555
444
222
333
444
555
111
333	1	25
222	2	23
555	3	20
444	4	18
111	5	16

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

 

[Taul]

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

=MAX(B3:B22)

Formula in cell B3

=COUNTIF($A$3:$A3,A3)

And copied down

Formula in cell C3

=IF(B3=$B$1,RANK(B3,$B$3:$B$22)+COUNTIF(B3:$B$3,B3)-1,"-")

And copied down

Formula in cell C3

=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

	A	B	C	D
1	Total Laps =	4
2	Bike #	Lap	Placing	Points
3	111	1	-	0
4	222	1	-	0
5	333	1	-	0
6	444	1	-	0
7	555	1	-	0
8	111	2	-	0
9	333	2	-	0
10	444	2	-	0
11	555	2	-	0
12	111	3	-	0
13	222	2	-	0
14	222	3	-	0
15	333	3	-	0
16	111	4	1	25
17	444	3	-	0
18	555	3	-	0
19	333	4	2	22
20	222	4	3	20
21	555	4	4	18
22	444	4	5	16
23		"Laps" uses Conditional Formatting
24	Type 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.

 

[nhk26]

Thank you I believe this should work. I will give it a go this weekend.