Jump to content

Spreadsheet for setups and performance in Career mode

Recommended Posts

I made an Excel spreadsheet to organize my setups, track my performance, and give me an insight on the AI levels.

There are two worksheets in it. The first is for the setups and performance. There's a table for each GP and it is sectioned with the car setup and a few items I find interesting in the Practice Programs and the Qualifying rounds. Alongside the car setups you have slots to fill with setups you find online and there are two columns that will give you (1) the median average for a given slider and (2) the mode average for it – i.e. which value is the most common one, if there is one. This way if you gather a bunch of setups but some of them are outliers, it is easier to identify which ones are on the "right" ballpark. Under the setups you'll be mostly filling in with fuel usage and time laps for the different practice programs and the qualifying. There are rows to compare those laps with (1) your teammate, (2) the 1st CPU in the grid and (3) the 6th CPU in the grid. It goes without saying but you can change those parameters to your heart's desire.

The second worksheet is to get a hold on the AI levels. Since your teammate is your first rival, I usually try to set the AI at a level where I'm in unceasing fight with him, either having an edge or infrequently topping him depending if I am the team’s 1st or 2nd driver. From what I heard, you should dial it up or down 1-2 notches for each 0.1 second difference, but this is my first racing game in more than a decade, it is hard as hell to get it right because I’m often improving during that session. To thwart that, instead of simply filling the worksheet with the race AI level, I do a simple average of the whole GP accordingly to the data on the main worksheet.

In this second worksheet you will just fill in the AI level, the qualy laps for you, your teammate and the 1st and 6th CPU, and their respective best laps on the race. There are formulas in the delta rows and formatting rules in the cells so it should automatically calculate the delta and highlight the whole thing dependent on it being a positive or negative delta. I did a chart with the qualy deltas just to ease the visualization part of it too, but it came out dreadful.

I am using a custom number format for the lap times on the whole workbook. It is [mm]:ss,000 or ss,000 depending on the context. Sorry about the comma, that’s the decimal separator I primarily use on my country and I wasn’t feeling like fiddling with Excel’s underbelly just to adapt it for the rest of the world.

The file is partially filled up and it is a mishmashly hodgepodge jumble of numbers. This is not the original file and I wasn’t meticulous on duplicating everything, but at least you’ll hit the ground running already.

I'm more of a Word guy, though, and it really shows. The workbook is as ugly as they come. But my intentions were good! Those with a keen eye may notice I used a few official hex codes when color coding some elements...

F1 2019 Setups and Career Performance.xlsx

  • Like 4
  • Thanks 2
  • Agree 1

Share this post


Link to post
Share on other sites

Thanks. I did some low key modifications and I am planning on adding a whole new worksheet with a breakdown of the setup sliders and quick fixes for common problems too. Hopefully I can put it out before F1 2020 release.

  • Like 1

Share this post


Link to post
Share on other sites

Hi Mario - Thanks for this

A few questions:

1. How do you input the times? if i Write "01:23:500" for example it does not like the format

2. How do you find out some things such as "max speed" and "tyre wear factor?"

3. Have you created anythign new yet?

Thanks!

 

Share this post


Link to post
Share on other sites

@DeMilo86

1. That's a hindrance from Excel itself, as it has some quirks about handling time formats. I made a custom mm:ss,sss format since the default one available won't go further than mm:ss,s. If you could take a screenshot it would be easier to give you a direct fix, but from start I can tell you to not use ":" as a separator for seconds-milliseconds as Excel think of it on a decimal scale, so use "." or "," instead. In your example it must be written as 01:23,500. Note that I'm using a comma because that's the default for my version of Office and the one made default on my spreadsheet file, maybe yours is a dot so it should be 01:23.500. If you need to make changes to the default decimal separator look here and here. Also worth noting that if you're trying to apply the time format style to a different cell, you must look for it on the Custom tab of the format settings ("More Number Formats..."). Scroll through it and select the [mm]:ss,000 one. The "[ ]" are there to denote that however big the number is, Excel must not convert it to hours. So you'll get 61:00,000 if you need to instead of 1:01:00,000.

2. I use a telemetry app! Shame on Codemasters for not having that data available somewhere in their interface. There is a topic on the F1 2019 forums with a list of third party telemetry apps available and there is one already for F1 2020 on Racedepartment. They're simple to set up but I have yet to find one with all the info I care about. Some veer to much into the telemetry analysis side of things and some are just plain dashboards with no ability to save the race data offline. 

3. I have! But I didn't have the time to make it presentable yet. Will do. This week, if all things go as planned. 

Share this post


Link to post
Share on other sites
Posted (edited)

Thanks for the heads-up - that's great advice

One more thing though - when creating a cell to show the difference in delta times, the simple formula of =sum(A1-A2) only works if A1 is faster than A2. If A2 is a faster time, the cell simply shows #### recurring. Even If I use your formatting using IF and ABS, the cell does not know if it's plus or minus.

Any ideas? Can't figure this one out.

Many Thanks!

Edited by DeMilo86

Share this post


Link to post
Share on other sites
Posted (edited)
21 minutes ago, DeMilo86 said:

Thanks for the heads-up - that's great advice

One more thing though - when creating a cell to show the difference in delta times, the simple formula of =sum(A1-A2) only works if A1 is faster than A2. If A2 is a faster time, the cell simply shows #### recurring. Even If I use your formatting using IF and ABS, the cell does not know if it's plus or minus.

Any ideas? Can't figure this one out.

Many Thanks!

The #### thing is not a calculating error, it is a "bro c'mon this is too tight I can breathe" error. Reduce font size or widen the cell and it should be fine.

Yeah, excel can be a bi ch sometimes.

Other than that I prefer to use the ABS function as Excel will go in stall mode if the result of the operation is a negative time. It does not like it one bit. I circumvent that with conditional formatting, as you can see.

Edited by marioho

Share this post


Link to post
Share on other sites
4 hours ago, marioho said:

The #### thing is not a calculating error, it is a "bro c'mon this is too tight I can breathe" error. Reduce font size or widen the cell and it should be fine.

Must be a large number - I widened the cell (G39) but still not working. This was however using "=SUM(C39-C37)" which I guess is wrong. Your ABS function is used in the cell above (G38) and works, just not formatted yet. I'm a stickler for detail - would love to know how to do it without the formatting workaround.

Excel really is a bi**h for this type of thing. Appreciate the assistance - As you can see I've been trying to create my own sheet. Waiting for some updates on the game before jumping into a new career.


Hashtags.thumb.jpg.d776361aefe9d9642fa14f87fb60b7ce.jpg

Share this post


Link to post
Share on other sites
Posted (edited)
1 hour ago, DeMilo86 said:

Must be a large number - I widened the cell (G39) but still not working.

Haha I chuckled hard here!

Yeah, I see what you mean. Looks like the sum you want to make will result in a negative time and Excel goes bananas if you ask him to display a negative time. Originally I was doing each operation by hand, but that obviously was not feasible at all. That's when the idea of using the Absolute function came to me, as it does not matter the position of the terms (i.e. C39-C37 or C37-C39) I could still make the same operation and thus replicate to the whole sheet with no need for adjustments. 

01:25,800	=SUM(A2-A1)	#########
01:22,235	=ABS(A2-A1)	00:03,565

Admittedly I didn't give it too much thought after I resorted to =ABS and conditional formatting (red background or red text for negative times based on =(A1>A2) type of formula, but this inherent disinclination to display negative times, I don't think there is much more we can do about it on Excel. 

Edit: Oh ***** looks like there is a simple fix indeed.

Edit #2: I thought I had posted it here already, but here's the work in progress with the new file.

Edited by marioho

Share this post


Link to post
Share on other sites
9 minutes ago, marioho said:

Edit: Oh ***** looks like there is a simple fix indeed.

Does that work for you? Doesn't work for me. Times still display as positive... 

Share this post


Link to post
Share on other sites

I did not give it a try yet, but you saying it kinda deflated me haha

 

Share this post


Link to post
Share on other sites

Would be interesting to work together and find some solutions - not just for this but other problems too. I've 'followed you' so feel free to send me A message anytime. In the meantime i'm going to go and nerd out on some more spread-sheeting!

  • Like 1

Share this post


Link to post
Share on other sites

@DeMilo86 I would most definitely love to! Did you check my second edit on the negative times post? I linked the work in progress on the second version of the spreadsheet. Lots of caveats and I have yet to decide on an overall format, but it's better than the original one from this topic.

Hit me if you have any feedback and we can get on with it!

Share this post


Link to post
Share on other sites
On 7/12/2020 at 12:08 PM, marioho said:

@DeMilo86

1. That's a hindrance from Excel itself, as it has some quirks about handling time formats. I made a custom mm:ss,sss format since the default one available won't go further than mm:ss,s. If you could take a screenshot it would be easier to give you a direct fix, but from start I can tell you to not use ":" as a separator for seconds-milliseconds as Excel think of it on a decimal scale, so use "." or "," instead. In your example it must be written as 01:23,500. Note that I'm using a comma because that's the default for my version of Office and the one made default on my spreadsheet file, maybe yours is a dot so it should be 01:23.500. If you need to make changes to the default decimal separator look here and here. Also worth noting that if you're trying to apply the time format style to a different cell, you must look for it on the Custom tab of the format settings ("More Number Formats..."). Scroll through it and select the [mm]:ss,000 one. The "[ ]" are there to denote that however big the number is, Excel must not convert it to hours. So you'll get 61:00,000 if you need to instead of 1:01:00,000.

2. I use a telemetry app! Shame on Codemasters for not having that data available somewhere in their interface. There is a topic on the F1 2019 forums with a list of third party telemetry apps available and there is one already for F1 2020 on Racedepartment. They're simple to set up but I have yet to find one with all the info I care about. Some veer to much into the telemetry analysis side of things and some are just plain dashboards with no ability to save the race data offline. 

3. I have! But I didn't have the time to make it presentable yet. Will do. This week, if all things go as planned. 

advancing game into a platform from a seasonal release to a subscription model is the only business model to support really cool ideas like this above. Shame. 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×