Excel sheets???

[Buttonfan]

Hi guys,

Just wondering how do you use excel sheets to calculate performance data?

Cheers.

EDIT: Sorry for posting this in the wrong part of the forum.

[Greyhead]

There are a few ways.

The way I use is to enter all the fastest laps and qualifying times of each driver during a season, then use these to find out who was fastest on average and assign them a particular grip value, then use a couple of formulas to find out how far behind all the other drivers are and convert it into a value for the grip. Then the variation in times for a particular driver can be used to work out the grip range.
It's all pretty time consuming but worth it to get accurate values for a carset.

I also use a shorter way for carsets where I can't find lap time data.
I enter the points that each driver finished the season with, assign a particular grip value for the champion, then work out how far behind the other drivers are. Then I use a conversion factor depending on the championship that tells the Excel file how many grip points each championship point is worth.

I understand that TdK goes further with his Excel files, using standard deviations and such.

[Buttonfan]

OK I've done the first 5 2008 races as a starter, just calculating their qualifying times. I've got the order for the grid by doing a formula for calculating the average lap time, but now I'm stuck on how to convert it to a value for the grip. How do you do that?

Thanks for your help so far

[Greyhead]

Well if you define an upper limit for the grip (in the original game this is 16384), then you can assign this to the fastest driver. Then if you get Excel to work out how much slower in percentage terms all the other drivers are, then you can use that percentage value to do the same for the grip.

For example, if you have Robert Kubica as the top driver, you assign 16384 to him.
Then if you have, say, Fernando Alonso as being an average of 1.3% slower across the season, you work out what 98.7% (100% - 1.3%) of 16384 is. Then that is Alonso's grip.

[Buttonfan]

Ok but how do I get Excel to work the percentage terms?

[Greyhead]

To work out a percentage, you use the formula

(change / original value) * 100.

where the original value is the time of the fastest driver in any given race or qualifying session, and change is how much slower the driver in question is.

Then you can work out an average for a particular driver of how much slower they are across a season compared with the fastest driver.

[Buttonfan]

Sorry mate, but the formula just isn't working, can you give me an example of what to type in or something?

Thanks for your help.

[Greyhead]

To explain it properly I'd have to go into the inner workings of Excel.

Basically if you have a cell set aside that finds the best time using the "min" function and assign a name to that cell, then you can work out all the gaps from that.
When I mentioned the percentage formula, the "change" part of it is just
(time of driver) - (overall fastest lap).
The "original value" is the overall fastest lap.

So let's say for arguments sake you have all the qualifying times of each driver in column D, and column E will be the one that has the percentage differences.
You put a cell somewhere out of the way and name it "pole". Then you enter the formula
=min(D2:D21)
This will search column D and find out the quickest qualifying time of the twenty drivers (obviously if there are more drivers you change the last number).
Then in column E, cell E2 you enter the formula
=IF(D2=0,"",((D2-pole)/D2)*100)
The blue part of this formula work out the percentage difference between the driver in row 2 and the pole sitter. The rest of it is just to tidy things up a bit, if no time is entered in D2 then E2 will stay blank.
Then use the "fill down" function from cell E2 to replicate this formula for the rest of the drivers.

Just keep playing around with it. It took me a long time to find out what worked for me.

[TdK]

I understand that TdK goes further with his Excel files, using standard deviations and such.

I work in a "kind of the same" way, with indeed Excel sheets to!

I work with 2 sheets a race. One for the qualifying, and one for the fastest race laps. I use Barcelona as a basis, because
i did not worked out Monaco yet...

First the qualification:

It works in 3 steps:
1.) You fill in all the times for each driver for Q1, Q2 and Q3. These times are in seconds, so no minutes added.
2.) The Excel sheet automaticly calculates the fastest driven time for each driver on the right.
3.) The sheet calculates in percent (%), at the bottom, what the differences between the drivers are.

Those percents are used later on.

The race:

The same as qualification, only now we have to enter one lap time, the fastest lap time for each driver, again only
enter the seconds.

This sheet also automaticly calculates percent values. The drivers in gray did not finish one lap, our did not
raced 25% of the race total laps. I use this 25% rule, to exclude large numbers who will mess up the drivers average because of the fact that lap times improve while the race is moving on, and cars get lighter.

The final stage is to get all the percent values of one driver from all race qualifications and fastest laps together. Now
let the sheet calculate you an average, and than you have a performance of a driver.

I only manual change/add grip values from each driver within the carset editor. I do also use sheets to calculate engine power, failure, random grip range. But i'm not going to tell all my secrets (now). I already showed to much...

[Buttonfan]

Hey TdK, thanks for your excel examples, but how do I get the sheet to automatically calculate the difference between the drivers for the qualifying times?

Cheers.

[TdK]

Hey TdK, thanks for your excel examples, but how do I get the sheet to automatically calculate the difference between the drivers for the qualifying times?

Then you will need to understand Excel better. My excel is in Dutch, so i don't know all the meanings in English, but from the lap times of each driver, i let the sheet automaticly (through a formula) figure out what was the fastest time of each driver.

It then has a formula which picks out the fastest laptime from all the drivers fastest laptimes. The differences between the fastest driver (laptime) and "the other" driver his time is then calculated into %. % is what you need for Gp2.

[Buttonfan]

Thanks, I'm kind of getting there now, don't think I'm doing it quite right but it's close enough, what formula do you use?

[AD]

The minimum function in English-speaking Excel should be like this

Code: Select all

=min(x1:x26)

where x1 and x26, for example, represent a range of cells where you've got the drivers' lap times. The result will give you the individual driver's fastest lap time.

Then collect all drivers' fastest lap times in a column and use the same function again to find the minimum -- now, you've got the absolute fastest lap.

Then, the performance difference of each driver should be measured in percent -- indicating how much slower everybody was compared to the absolute best. The function for that should be dividing the fastest lap time by the current driver's time.

An example:

Code: Select all

Absolute best: 1:13,12 min
Current: 1:14,45 min

Absolute best / Current = 1,78 %

These percentages, as I understand it, can then be used to find the right measures and differences for the drivers' performance values.

[Greyhead]

Yeah that's pretty much how I work it out. Basically, like with most things GP2-related, the best thing you can do is experiment, Excel's help function is pretty useful (unlike most Microsoft programs) and if you still don't know how stuff works, try Googling it, there's generally plenty of help available. I didn't have a clue how it worked other than very basic functions, but by playing about with it I found out how it worked.