Stock car racing points championship calculator

[bozz]

Hi all,

Although I use Excel quite a bit, I am only able to do fairly basic stuff. I have been wracking my brains trying to come up with a points championship calculator for a stock car league I am involved in. Currently I do the calculations manually. Here's what I need help with :

Basically, I need to enter the results of each race. Each race records the top 10 finishers and assigns points to each individual driver. Each placing will be recorded / entered as the driver's number. Ideally, Excel will then calculate the amount of points scored by each driver during the race meeting and return a list containing the points scorers highest to lowest.

An example of a race result to be entered : 131 281 42 355 575 439 161 373 9 77 (each number is a driver)

A heat race is scored 12pts, 10, 8, 7, 6, 5, 4, 3, 2, 1
A feature final is scored 25pts, 20, 16, 14, 12, 10, 8, 6, 4, 2

To complicate things slightly, we sometimes have an extra race after the feature. The winner of the feature scores double points if he places in this extra race. All other positions are scored as per a heat race.

Is it also possible to calculate prize money for each driver ?

I have looked all over the net and on this forum but I cannot see anywhere an example where values can be assigned to number data (unless I am being dumb?)

Any help would really be appreciated. If someone could point me in the right direction, I will have a play although I will struggle to understand anything too complicated - but I will do my best !!

 

[DonkeyOte]

bozz, Welcome to the Board

I would advise you do something like this:

have a sheet called "Results"

In B1:B3 enter HR, FR, SR respectively
(Heat Race, Feature Race, Speical Race)

in C1:L3 enter the default points per position:
row 1 is HR so C1 = 12, D1 = 10 to L1 = 1
row 2 in FR so C2 = 25, D2 = 20 to L2 = 2
row 3 is SR so C3 = 12, D3 = 10 to L3 = 1

Once you have this set up I would then advise you record your results in a subsequent table as follows :

In A5 -- header: Date
In B5 -- header: Race Type
In C5:L5 -- P1 to P10
In M5:V5 -- P1 Pts to P10 Pts

Then row 6 onwards enter your race results with date of race in A, type of race in B and race driver no's in C:L as per their positions.
Note: for each day you should have three races (even if no SR conducted) -- ie B6 = HR, B7 = FR and B8 = SR
(I'm assuming for purposes of this demo you have only 1 race of each type per day)

Now you need formula to generate appropriate point values in M:V

In M6:V6 (where B6 = HR) -- below is for M6 to be copied across:
=IF(C6=0,0,C$1)

In M7:V7 (where B7 = FR) -- below is for M7 to be copied across:
=IF(C7=0,0,C$2)

In M8:V8 (where B8 = SR) -- below is for M8 to be copied across:
=IF(C8=0,0,C$3)*(1+(C8=$C7))
(this will double points per your requirements if FR P1 is placed in SR)

You can repeat these 3 rows per day for each day of results.

For purposes of this example I'm assuming 4 days of results so 12 rows of data in my master results table (A6:V17) -- (13 including header row of A5:V5)

Given you have master results table you can now look to build summary:

Create a new sheet "POINTS"

In A1 enter "DRIVER"

In A2 copied down for as many rows as required:
=IF(MAX($A$1:$A1)=MAX(RESULTS!$C$6:$L$17),"",SMALL(RESULTS!$C$6:$L$17,COUNTIF(RESULTS!$C$6:$L$17,"<="&A1)+1))

This will give you a dynamic listing of all the drivers in your results tab (ie all those placed in history)

in B1 enter "TOTAL POINTS"
in B2 copied down for as many rows as required: (same as A)
=SUMIF(RESULTS!$C$6:$L$17,$A2,RESULTS!$M$6:$V$17)

This will give you total points for given driver.

Even if this is not exactly what you require the methods should hopefully point you in the right direction.

HTH.

 

[bozz]

Hi lasW10,

Thank you for your excellent explanation. I think even I should be able to cobble something together from your guidance and, for that, I'm really grateful.

I will post my effort in due course.

Cheers !

 

[DonkeyOte]

Thought I'd try and provide some screenshots to help make sense of my post!

The below would be the "RESULTS" sheet table(s):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		HR	12	10	8	7	6	5	4	3	2	1
2		FR	25	20	16	14	12	10	8	6	4	2
3		SR	12	10	8	7	6	5	4	3	2	1
4
5	Date	Race Type	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P1 Pts	P2 Pts	P3 Pts	P4 Pts	P5 Pts	P6 Pts	P7 Pts	P8 Pts	P9 Pts	P10 Pts
6	19-Jul	HR	569	402	439	442	399	199	339	578	426	22	12	10	8	7	6	5	4	3	2	1
7	19-Jul	FR	131	281	42	355	575	439	161	373	9	77	25	20	16	14	12	10	8	6	4	2
8	19-Jul	SR	131	281	42	355	575	439	161	373	9	77	24	10	8	7	6	5	4	3	2	1
9	20-Jul	HR	178	286	345	573	198	32	209	388	193	526	12	10	8	7	6	5	4	3	2	1
10	20-Jul	FR	131	281	42	355	575	439	161	373	9	77	25	20	16	14	12	10	8	6	4	2
11	20-Jul	SR											0	0	0	0	0	0	0	0	0	0
12	25-Jul	HR	245	478	193	402	598	167	339	94	156	129	12	10	8	7	6	5	4	3	2	1
13	25-Jul	FR	131	281	42	355	575	439	161	373	9	77	25	20	16	14	12	10	8	6	4	2
14	25-Jul	SR											0	0	0	0	0	0	0	0	0	0
15	26-Jul	HR	145	345	375	275	285	412	24	112	500	575	12	10	8	7	6	5	4	3	2	1
16	26-Jul	FR	131	281	42	355	575	439	161	373	9	77	25	20	16	14	12	10	8	6	4	2
17	26-Jul	SR	581	76	21	177	120	370	570	503	481	591	12	10	8	7	6	5	4	3	2	1
RESULTS

Below is small(ish) sample of final POINTS table:

Excel Workbook
	A	B
1	Driver	Points
2	9	18
3	21	8
4	22	1
5	24	4
6	32	5
7	42	72
8	76	10
9	77	9
10	94	3
11	112	3
12	120	6
13	129	1
14	131	124
15	145	12
16	156	2
17	161	36
18	167	5
19	177	7
20	178	12
21	193	10
22	198	6
23	199	5
24	209	4
25	245	12
26	275	7
27	281	90
28	285	6
29	286	10
30	339	8
POINTS

HTH

 

[bozz]

Coool ! it works a treat lasW10 - thanks.

Is there a way I could calculate the prize money for each placing and display that in a third sheet (Prize Money) ?

 

[DonkeyOte]

What are the prize money "rules" ?

 

[bozz]

Well, this is where it could get complicated !

Basically, each race meeting has a minimum of 3 races and a maximimum of 5. Here are the scenarios :

2 heat races and 1 feature final race
or
2 heat races and 1 feature final race and an all comers
or
3 heat races and a feature final

I can have a different work sheet for each scenario......with your brilliant help I reckon I can do that myself.

The prize money will vary on the amount of cars that race but I think I can possibly sort that out. Here is the most popular payout schedule (it isnt much - we race for the love not the money !)

Heats 12 10 8 7 6 5 4 3 2 1
Final 20 15 10 9 8 7 6 5 4 2
All comers race does not carry prize money.

 

[DonkeyOte]

Bozz, I've made some changes to the layout etc and formulae of your "proof of concept".

Note this revised layout permits you to have multiple HRs etc... though if you do have an All Comers (SR) it must be listed below the Feature Race (FR).

Note I have created two named ranges:

B2:L4 = pts_table
B7:L9 = usd_table

Both will be referred to in later formulae.

Below is the results tab which will calculate points / prize money (based on the simple scenario) -- I've hidden some columns (P2 to P9 for both points and usd) so this can be displayed...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	V	W	AF	AG
1	Points	Race Type	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10
2		HR	12	10	8	7	6	5	4	3	2	1
3		FR	25	20	16	14	12	10	8	6	4	2
4		SR	12	10	8	7	6	5	4	3	2	1
5
6	Money	Race Type	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10
7		HR	12	10	8	7	6	5	4	3	2	1
8		FR	20	15	10	9	8	7	6	5	4	2
9		SR	0	0	0	0	0	0	0	0	0	0
10													Points		USD
11	Date	Race Type	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P1	P10	P1	P10	Table_Row
12	19-Jul	HR	569	402	439	442	399	199	339	578	426	22	12	1	12	1	1
13	19-Jul	HR	518	149	68	538	596	128	217	424	296	453	12	1	12	1	1
14	19-Jul	FR	131	281	42	355	575	439	161	373	9	77	25	2	20	2	2
15	19-Jul	SR	131	281	42	355	575	439	161	373	9	77	24	1	0	0	3
16	20-Jul	HR	178	286	345	573	198	32	209	388	193	526	12	1	12	1	1
17	20-Jul	FR	131	281	42	355	575	439	161	373	9	77	25	2	20	2	2
18	20-Jul	SR											0	0	0	0	3
19	25-Jul	HR	245	478	193	402	598	167	339	94	156	129	12	1	12	1	1
20	25-Jul	FR	131	281	42	355	575	439	161	373	9	77	25	2	20	2	2
21	25-Jul	SR											0	0	0	0	3
22	26-Jul	HR	145	345	375	275	285	412	24	112	500	575	12	1	12	1	1
23	26-Jul	FR	131	281	42	355	575	439	161	373	9	77	25	2	20	2	2
24	26-Jul	SR	581	76	21	177	120	370	570	503	481	591	12	1	0	0	3
RESULTS

A sample of the formulas is listed below
Note re: hidden columns etc that the formulas for P2 to P9 are as per P1
(ie can copy P1 formula across and down for that particular section (ie points/usd))

Excel Workbook
	M	V	W	AF	AG
10	Points		USD
11	P1	P10	P1	P10	Table_Row
12	12	1	12	1	1
13	12	1	12	1	1
RESULTS

Your POINTS tab can then have additional column added to sum $ as well as points:

Excel Workbook
	A	B	C
1	Driver	Points	USD
2	9	18	16
3	21	8	0
4	22	1	1
5	24	4	4
POINTS

HTH

 

[bozz]

lasw10, you are a genius......not only for your expert excel knowledge but for explaining it so well.

I now have 3 separate workbooks containing 3 different race meeting scenarios. With the help of some macros I have added buttons to sort the Points worksheet into points scoring order and or money order.

Our racing league pays money to the drivers depending on how many turn up. So, for example, if 27 race a prize fund of £270 is paid out. Using macros I have been able to have the four prize money scenarios at the click of some buttons to swap them in and out of the worksheet to suit.

I also 'hid' the workings out cells by having them in white just to neaten things up a bit as I do not know how to move all that stuff to another worksheet without messing up all the lovely formula's.

I would like thank you once again for taking the time to explain, calculate, convert and post all the info you have. If you drank down the road from me I'd buy you a beer or even two. Thanks.

 

[IrishTeri81]

Is there anyway for these formulas to work with car numbers that contain letters? We have multiple instances where 2 different cars in the same class have the same number, so to make it easier, we'll add a letter after the number to tell the two apart. I just love this template, but it doesn't seem to recognize car "32x." Is there anyway this could work for my situation? Thank you so much in advance!