Logan95
Logan95 New Reader
5/16/22 11:30 a.m.

I have created a handful of spreadsheets over the years to analyze my autocross performance, and compare it to competitors on a per-event basis. Usually comparing my times to those of my class competitors, Pro class, and top PAX and top RAW drivers to try and gauge my relative performance to "known fast drivers". 

 

Question is: how do I build a spreadsheet that is more powerful, more efficient, or more useful in tracking an individuals autocross performance relative to everyone else? I'd like to be able to choose a driver, and see a trend line of their performance over time, relative to the top performers at each event in which they competed. It should allow comparing 2+ drivers to each other as well, by overlapping their trendlines or by other comparison methods. This is not an autocross question, but more of a Data Analyst and Excel question. 

 

I'm okay with ignoring/ommitting cone and DNF penalties, or separating them at least. It would be great if this tool could allow any competitor to be analyzed....whether they attend every single event or just 2 events (need at least 2 points to generate a line). 

 

We use AXware locally, and I have data files for every event from the past decade or so. Three files many of you are familiar with: Final (by class), RAW, and PAX. I can import any of the three and reformat as needed to fit the needs of the Excel tool/spreadsheet I'm asking about. 

spacecadet (Forum Supporter)
spacecadet (Forum Supporter) UltraDork
5/16/22 12:15 p.m.

You'd need to convert the results into raw data dumps in excel or google sheets, then focus on a few top drivers you want to track each month, and the drivers you want to track relative to them.

in simple terms.

you're going to need a few tables to do this properly.

track pax position of each driver each event, where the drivers are listed going down the table, and the events are going across the table from left to right.

Then do the same with the pax time differential to top pax, either as a function of time or %.

So the data set would look something like this and these are the formulas you would use for example.



This is how that sheet looks when you let the formulas work



Then you just graph the data and it looks like this.




This is all crudely put together.. but this should help you look at the results in a manner that allows you to see your own growth over time.

Pete Gossett (Forum Supporter)
Pete Gossett (Forum Supporter) MegaDork
5/16/22 12:26 p.m.

I'd put it all in a database. Personally I find it easier than working with Excel, but it's probably as much as situation of familiarity as it is function. 

ProDarwin
ProDarwin MegaDork
5/16/22 12:27 p.m.

Mostly, as Spacecadet stated.  I would import each event into its own sheet and create a sheet that will extract the key info from each driver into a table.  As you do more events, you just add another sheet for each one.

Then just graph that.

 

I'm here for the excel :)

bobzilla
bobzilla MegaDork
5/16/22 12:30 p.m.

I just go fast as I can go. 

Tom1200
Tom1200 UltraDork
5/16/22 2:21 p.m.

Interesting: I use spreadsheets all day every day for work as well as racing and this is one I would not have ever thought to do. I've got them for various tracks and projected improvements and what times might be etc.

I wouldn't do anything beyond raw times and the gap to the next driver. I might do PAX times on a sheet to see where I'm at.

The issue with comparing to other drivers is there are so many variables; heat cycles on their tires, did they have an off day etc.?

I do love the geekery / obsessiveness of it though.

You'll need to log in to post.

Our Preferred Partners
Y3RJlugnxsfJLOgYeeTwOiNKngbLDZXTKk5jhGwEsXTK9ZFhlFMNq43Nxu4HIVlb