Stock car racing points championship calculator

bozz

New Member
Joined
Jul 20, 2008
Messages
5
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 !!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
 
Upvote 0
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 !
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUV
1HR121087654321
2FR2520161412108642
3SR121087654321
4
5DateRace TypeP1P2P3P4P5P6P7P8P9P10P1 PtsP2 PtsP3 PtsP4 PtsP5 PtsP6 PtsP7 PtsP8 PtsP9 PtsP10 Pts
619-JulHR56940243944239919933957842622121087654321
719-JulFR131281423555754391613739772520161412108642
819-JulSR13128142355575439161373977241087654321
920-JulHR17828634557319832209388193526121087654321
1020-JulFR131281423555754391613739772520161412108642
1120-JulSR0000000000
1225-JulHR24547819340259816733994156129121087654321
1325-JulFR131281423555754391613739772520161412108642
1425-JulSR0000000000
1526-JulHR14534537527528541224112500575121087654321
1626-JulFR131281423555754391613739772520161412108642
1726-JulSR5817621177120370570503481591121087654321
RESULTS


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

Excel Workbook
AB
1DriverPoints
2918
3218
4221
5244
6325
74272
87610
9779
10943
111123
121206
131291
14131124
1514512
161562
1716136
181675
191777
2017812
2119310
221986
231995
242094
2524512
262757
2728190
282856
2928610
303398
POINTS


HTH
 
Upvote 0
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) ?
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJKLMVWAFAG
1PointsRace TypeP1P2P3P4P5P6P7P8P9P10
2HR121087654321
3FR2520161412108642
4SR121087654321
5
6MoneyRace TypeP1P2P3P4P5P6P7P8P9P10
7HR121087654321
8FR2015109876542
9SR0000000000
10PointsUSD
11DateRace TypeP1P2P3P4P5P6P7P8P9P10P1P10P1P10Table_Row
1219-JulHR569402439442399199339578426221211211
1319-JulHR518149685385961282174242964531211211
1419-JulFR131281423555754391613739772522022
1519-JulSR13128142355575439161373977241003
1620-JulHR178286345573198322093881935261211211
1720-JulFR131281423555754391613739772522022
1820-JulSR00003
1925-JulHR245478193402598167339941561291211211
2025-JulFR131281423555754391613739772522022
2125-JulSR00003
2226-JulHR145345375275285412241125005751211211
2326-JulFR131281423555754391613739772522022
2426-JulSR5817621177120370570503481591121003
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
MVWAFAG
10PointsUSD
11P1P10P1P10Table_Row
121211211
131211211
RESULTS


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

Excel Workbook
ABC
1DriverPointsUSD
291816
32180
42211
52444
POINTS


HTH
 
Last edited:
Upvote 0
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.:biggrin:
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top