Creating season on spreadsheet

How do you do a season on a spreadsheet so that it calculates and sorts points and sorts drivers? Or is there a better and easier program out there? Know basic spreadsheet. :p

Edit: Got it. Spreadsheets...Totally over complicated.
 
Last edited:
Hi Gavin

I couldn't resist having a play with this in the end. Please take a look at the attached update and see if it does what you're after.

The penalties are just deducted from the overall total, so you can get negative total points. I had started by thinking that the points should be deducted from the individual races and, if that's what you actually had in mind, it would be relatively simple to change it around to work this way.

The actual finishes, not the adjusted points, are still used to determine driver order in the case of ties.

As this is a first draft, and I haven't had the chance to do any real testing, there will be bugs (there always is!).

David

First I want to tell you I love your spreadsheet! It's almost exactly what I am looking for. We have multiple divisions of cars and I would like to have separate tabs for each division. When I copy the tabs, the standings tab is still pulling from the original. I'm not sure how to edit the formula to pull from the new tab.
 
Hello Everyone

Firstly, I would like to apologise most sincerely to Ratsgetfatt and Angus for not replying to their posts. For some reason I didn’t receive (or didn’t notice) the icon notifying me that there had been a reply and wasn’t aware that the thread was still receiving responses. It was only today, when amandajo quoted my previous post, that I received a notification and realised that there was additional posts on the thread.

Rats, I’m not a NASCAR follower, nor have I played NASCAR Heat 4, so I’m not entirely sure what Stages and Playoff points are and how they function. If you can explain the process, I’ll certainly see if they can be implemented.

Angus, the Points Adjustment row is near the top of the table on the Results page, just below the headings. You need to make sure that you have enabled macros and selected ‘Yes’ for the Points Adjustments option on the Standing page before it will become visible. Adding more players will require adding additional rows onto the various tables, changing some formulas within the Data page and updating some of the macros. How many are you looking to have?

Amandajo, I presume you’re needing a separate Results and Standings page for each division? As well as copying these sheets, you would need to copy the hidden Data sheet and then use find and replace to point all the references within the formulas at the new sheets. There would also need to be some changes to the macros. How many divisions do you have? Would it not be easier just to have a separate sheet for each division, or are you looking to have all division's tables displayed together on one sheet?

David
 
Last edited:
Hi Angus

The current version should be able to handle 46 players and 30 teams (unless you've found another bug!). You'd asked about more than 50 players, so are you saying you need an extra 35, or is it 35 teams you need?
 
Hello Angus

Here’s the first draft of the larger version of the spreadsheet (L for large). I opted for a bit of headroom and doubled the number of entries to 92 drivers and 46 teams in the end. Some things to note are:

It is now very large file, with a considerable amount of calculation going on. I’m not sure how well it will run once there’s a lot of data in it. If it proves too much, it shouldn’t be a huge job to take some entries out.

Although the changes weren’t complex, there was still quite a bit to do and I can’t promise that I’ve managed to change every formula and macro to match the increase in drivers. I’ve done some testing, but haven’t the patience to do a full run through, which would require inputting results for all 92 drivers for all 30 races. So I apologise for any bugs which are present. If you find anything, let me know and I’ll implement a fix asap.

There’s now an option to limit the number of rows displayed in the ‘Results’ and ‘Standings’ tables. Simply enter a number between 12 and 92 in the ‘Number of Drivers’ box on the Standings page.

A while back I created a version of the spreadsheet which didn’t automatically reset the view as the various options were selected or changed, just as a test. Unfortunately, I accidentally started working on this version and was too far through with the changes before I realised, so the update is based on this version. This means that when you change the various options on the ‘Standings’ page, such as ‘Number of Races’ or ‘Show Teams’, you need to hit the ‘Reset’ button afterwards to see those changes affect the sheet. A red ‘*’ will appear next to any items which have been changed, as a prompt that you need to ‘reset’. If this is a problem, then let me know, as it shouldn’t be too much work to put the original code back in.

I’ve fixed an issue with the teams function which meant that any drivers not assigned to a team would be grouped as team ‘0’ and included in the standing table. These drivers will no longer appear in the teams list.

I also discovered that when I added races 21 to 30, I didn’t adjust part of the ranking process which meant that these races were excluded from the calculation of the 8th ranking function (next best 5 finishing positions after 6th). This has now been corrected.

You can now have up to 92 scoring places.

The penalty points function is now a ‘Bonus / Penalty Points’ option. Penalty points are input as negative amounts and any bonus points as positive numbers.

I’ve updated the old ‘small’ version of the sheet to include the ‘number of drivers’ and bonus/penalty points options, and fixed the teams and ranking faults. This is attached for anyone who wants to use it. If anyone has a sheet already in use and they think either fault might affect the season, let me know and I’ll explain how to fix it, or arrange for them to email the sheet to me for correction.

As always, change the file extension back to xlsm after downloading.

David
 
Great work on the sheet.
Any further updates on this? For some reason trying it with google sheets and OpenOffice doesn't seem to change the race driver or races down.
Will try it on a friends proper Microsoft Office tomorrow.
 
Last edited:
Hi Dave

I don't know about Google Sheets, as I've never used it, but I did try to convert it to OpenOffice (well, LibreOffice actually), but it wasn't a great success. In theory, it should work okay, as LibreOffice uses much of the same code as VBA. However, whilst most of the macros worked, I couldn't stop the sheet from flickering constantly once a command had been run. What I ended up doing was removing all the code and revealing all the optional items, which resulted in a very large sheet with a lot of scrolling around necessary (even on my 3440×1440). Of course, there's nothing to stop people manually hiding all the unused columns and rows themselves, but it rather lacks finesse and upsets my OCD nature! Also, LibreOffice's conditional formatting options aren't as good as MS Office's, so there's some comprimise to be made there. I'll have another play with it and post the result in a few days, if that's okay?
 
Hey David this is exactly what i've been looking for, i just have a tiny problem with the sheet. our league will run for 7 races however once i set that in the s or l sheet the results page goes all wonky, 8 seemed to be the minimum so then i try and set the first half for 5 from 7 and again the results page goes weird. am i doing something wrong. hope you can help
 
Hi faffie. You're quite right! it seems that when I added the Teams column I failed to change one of the formulas involved in hiding the various columns and this is the result. It's now fixed and the corrected version is attached (change the extension to .xlms as always). Apologies for the error.

I've also now converted the file to .ods format. LibreOffice seem to have tightened up their VBA routines and all the macros now seem to work without a hitch. It won't work in OpenOffice Dave, as this has no VBA implementation, but LibreOffice is free as well and a much better package in my onion (although still quirky and buggy, give me MS Office any day!). And you'll still need to change the file extension (to .ods).
 

Hey David, I've been using your spreadsheet for a while now and it's been a real life saver when it comes to running AI championships through single race when the sim in question has no custom championship suite. That you've expanded the number of possible drivers is a welcome surprise!

I'll mention that yes, it does work in Google Sheets, though Sheets itself is a bit finicky to deal with.

My question/request: Is it possible to double the number of events from 30? I've got my eyes set on running a massively long championship (30 rounds, 66 races) and trying to double the number of events is well beyond me, as I've learned the hard way. Haha
 
Hi Jon. It's certainly possible, but will require quite a bit of work. I'm fairly busy at the moment due to my caring responsibilities (I haven't sim-raced for over 4 months), but I'll take a look when I get the chance.

I wasn't aware that Google Sheets ran it's own VBA implementation and it would be interesting to see how much MS code would transfer over. I don't have a Google account myself and I'm not even considering converting the spreadsheet to that format at present.
 
Hi Jon. It's certainly possible, but will require quite a bit of work. I'm fairly busy at the moment due to my caring responsibilities (I haven't sim-raced for over 4 months), but I'll take a look when I get the chance.

I wasn't aware that Google Sheets ran it's own VBA implementation and it would be interesting to see how much MS code would transfer over. I don't have a Google account myself and I'm not even considering converting the spreadsheet to that format at present.

I believe I've actually found a sufficient workaround thanks to a past implementation you made:

Theoretically, I can run the first half of say, a 30 round, 60 event championship, and the spreadsheet as it is would cover the first 30 events. Then I would take down the points after event #30, start a new worksheet, and apply them as bonus points to the first event of the second half, then go on with doing the second half of the season. Sort of like how some soccer leagues have a "Spring" and "Fall" schedule that combines to make a full season.

I believe this would be a valid solution for any championships that may be NASCAR length (36 races per year) or that incorporate dual or even triple events per round, ala touring cars. The 30 event maximum as it is fits perfectly to my 34" ultrawide without having to sidescroll, so it looks very neat as is. haha

Thank you for considering my request, but don't rush yourself to fulfill it--you've definitely done more than enough just providing this worksheet in the first place.
 
Last edited:
I do have a quick question though. It's been a while since I've messed with creating my own point systems and they were on Google...I'm using Excel now and it seems it won't let me edit the points table other than the User column. I might've just forgotten how to do it, or it was a lot easier to create points tables on Google Sheets. Nothing got messed up on your end, did it?
 
Only the 'User' column is unprotected by default Jon, Google must be bypassing the sheet protection somehow. Just unprotect the sheet though (in the review tab - there's no password) and you can change whatever you like. If some of the default point systems are wrong, let me know and I'll correct my copy.

Your 2-sheet system will work, but might occasionally throw up some peculiar rankings, due to only the last set of races being used to determine positions in the event of a tie.
 
I’m really, really sorry about this everyone, but I’ve discovered a major bug in the sheet. I don’t know when this was introduced, but will affect more than just the last release.

The fault is that races 21 to 30 will not record the correct points for the first driver in the list, no other drivers are affected and it shouldn’t affect split seasons. I’d been messing with a new idea a while back, making changes in row 1 only as a test and then forgotten to revert the formulas back when I decided not to pursue the change.

It is simple to fix, if anyone wants to correct a sheet currently in use. Just do the following:
  • Reveal the hidden Data sheet by right clicking on any sheet tab, selecting ‘Unhide…’ and then clicking ‘OK’.
  • Go to the Data sheet, select cells AX7 to BG7 and then ‘copy’ (Ctrl & c, or from menu).
  • Click on cell AX6 and ‘paste’ the copied data (Ctrl & v, or from the menu).
  • Hide the Data sheet by right clicking on its tab and selecting ’Hide’ (this step isn’t essential, but will offend my OCD if not performed!).
If anyone doesn’t feel up to this and would like me to correct their sheet for them, please PM me.

Again, my sincerest apologies for this error and I do hope it hasn’t affected anyone’s championship.

I’ve attached copies of the 3 main sheets with the fix already applied. As usual, change the extension back to .xlsm or .ods as appropriate.

Jon, I actually spotted this error whilst looking at a possible solution for your problem. It occurred to me that, in part, the sheet already deals with 60 races, as the first and second halves both have 30 possible rounds. It’s just that, at present, they can’t overlap and the total cannot exceed 30. I thought that I could add a second Results sheet and, with a few additions in the Data sheet, have it able to deal with 60 in total. After initial work, I still think this is feasible and less work than simply adding the capacity for a further 30 races. Of course, it’s still only 60 in total, whereas you’re looking for 66, so I’ll still need to add some extra race slots to make it work properly for you.
 

Attachments

  • Season Manager-L.xls
    633.4 KB · Views: 210
  • Season Manager-S.xls
    343.3 KB · Views: 181
  • Season Manager-ods.xls
    445 KB · Views: 148

Latest News

Online or Offline racing?

  • 100% online racing

    Votes: 76 7.3%
  • 75% online 25% offline

    Votes: 111 10.6%
  • 50% online 50% offline

    Votes: 151 14.4%
  • 25% online 75% offline

    Votes: 288 27.5%
  • 100% offline racing

    Votes: 418 39.9%
  • Something else, explain in comment

    Votes: 4 0.4%
Back
Top