Developing a helpful championship spreadsheet/database

[zantkiller]

Hi. I'm going to try and develop a standard database/spreadsheet that can be used by series makers to help store driver info, track info, points, championship positions and other important things for my A-Level IT project.
I get the general feeling that most people just use systems that they have bashed together or even use a notepad and pencil and try calculate everything themselves.
Is this the case? And if so would this database/spreadsheet be really useful?

Any pre-existing system that you guys use as examples would be brilliant

[AndreaModa]

Welcome to the forum! You'll find that this is where pretty much everything goes: http://f1alternate.wikkii.com/wiki/Main_Page

If you mean statistical analysis or things like that, I don't think anyone uses anything like that, the most complicated methods have been people rolling dice to settle driver moves, engine deals, etc!

[tristan1117]

This would be extremely helpful. Wizzie can attest to my struggles in keeping the F1RGP2C standings accurate and I have another championship where everything is compiled in a set of five battered notebooks with illegible writing. I usually design an Excel spreadsheet to keep everything straight in terms of race results but I haven't developed a system to track overall statistics yet. And track info, specific times, fastest laps and pole times are completely ignored. A computer program would help a lot.

[Nuppiz]

My note systems at the moment are like this:
RoLFS:
Notepad for pretty much anything
Team budgets only on the forums (which I should backup soon...)

LGPS:
Spreadsheet for car/driver upgrades
Notepad for upgrade prices
Team budgets are, again, calculated in my head and are only available here on the forums
Regular backups of all team & driver data on an external HDD

I'd most prefer a tool which can help me keep track on the team budgets better - it's still simple in RoLFS because the amounts are small and purchases aren't that common due to high prices, but in LGPS I have to update the budgets after each race and almost every time I find mistakes left from my previous updates.

The wiki is good for keeping up on the points and standings, but for it to be effective it has to be updated after each race which is sometimes quite a tenuos job because of all the wiki formatting involved (even though mostly it's just copy paste with minor alterations). So a tool which could be used to keep track of the results would be nice as well.

[zantkiller]

Sorry for the very long time for a reply but I have been quite busy.
I have done some prototype work just to try some statistical analysis with a dynamic graph which allows you to highlight a drivers progress throughout a season and select 2 races you want to see the result in-between.

For the purposes of testing I've just used the 2012 F1 results


As you can see there is a slight problem with Excel refusing to acknowledge drivers who have a last name which begins with 'R' but I hope I can iron this out tomorrow.

But the important question is whether this kind of statistical analysis would be useful for you guys and series managers.

[pasta_maldonado]

Sorry for the very long time for a reply but I have been quite busy.
I have done some prototype work just to try some statistical analysis with a dynamic graph which allows you to highlight a drivers progress throughout a season and select 2 races you want to see the result in-between.

For the purposes of testing I've just used the 2012 F1 results


As you can see there is a slight problem with Excel refusing to acknowledge drivers who have a last name which begins with 'R' but I hope I can iron this out tomorrow.

But the important question is whether this kind of statistical analysis would be useful for you guys and series managers.

It would be very helpful for me, that's for sure How would I input data into the spreadsheet though? Would I simply put in race results and it would do the rest?

[SuperAguri]

Some people here are a bunch of luddites at times.

I did post a bunch of formulas a while back to help people with point scoring on spreadsheets as there were mistakes and got a muted responce with most people saying a notepad and paper is all they need, when they were a lad, they used to live in a hole in middle of ta road...

I have also created formulas (as opposed to a script) that will automatically generate a wiki table for race result times or even a points table (with colour too), although it is a stupidly long formula...

[pasta_maldonado]

Some people here are a bunch of luddites at times.

I did post a bunch of formulas a while back to help people with point scoring on spreadsheets as there were mistakes and got a muted responce with most people saying a notepad and paper is all they need, when they were a lad, they used to live in a hole in middle of ta road...

I have also created formulas (as opposed to a script) that will automatically generate a wiki table for race result times or even a points table (with colour too), although it is a stupidly long formula...

I really like the sound of that Super! Could I possible use it? (All credits to yourself naturally)

[zantkiller]

It would be very helpful for me, that's for sure How would I input data into the spreadsheet though? Would I simply put in race results and it would do the rest?

Currently I just input the results in this table here (in the Red square). It then assigns points for the corresponding positions which I can change in the 2 columns on the right (in the Blue square), that means I can change what positions are worth on the fly and it immediately shows the new results.
It then adds it all up together, makes a total points (in the Green square) and makes the graph on it's own.


In the future I would have a proper input screen for each race and you just input each driver position for that race and not into a horrific table. It would then do all the complicated analysis immediately.
Or with rFactor I might be able to extract results from the .xml files which the game produces.
That would be cool.

[SuperAguri]

I shall tidy up my formulas and paste them with some information over the next few days.

Although doing a point table like Zantkiller has done is quite easy it is basically.

=COUNTIF(range of results;position)*cell for points for position and then add it to all the points positions.

On his table it would be (for the top three results for Sebastian Vettel)

=COUNTIF(D2:W2;1)*AA2+COUNTIF(D2:W2;2)*AA3+COUNTIF(D2:W2;3)*AA4

You can then copy and paste this for all the other drivers, you may also notice the fomula is cut and paste, so you do the first countif and copy it, pasting it for each postion, changing the position and point cell as you go along.

Another way is to have cells which show wins, 2nds, 3rds, etc, using COUNTIF and simple maths on the cells.

[zantkiller]

Although doing a point table like Zantkiller has done is quite easy.

Yeah when I first tried making one of these with my limited understanding of Excel I decided to forget easy and go and produced this horrible formula.

=VLOOKUP(D2,Z$2:AA$28,2,FALSE)+VLOOKUP(E2,Z$2:AA$28,2,FALSE)+VLOOKUP(F2,Z$2:AA$28,2,FALSE)+....

[roblo97]

that could come in quite handy

will it work on excel 2003

[good_Ralf]

I like this formula very much and would love to use it but keep going wrong somewhere. I find this confusing.