Excel question, about TimeTrials and 'codes'...

[YukinoSuzuka]

Hello all,

For a small Dutch forum I organise a TimeTrial championship, and like last season, I did it all manually.. But I know Excel (OpenOffice to be exact) is smarter than that and can do all the work for me, I've only to fill in the times and my spreadsheet will count all the differences and positions etc etc.

Is there anybody that can help me with a code/file to make the work a bit easier ?

 

[eran0004]

What functionality do you want? Perhaps you have some picture of what you did last season, for reference?

 

[Luminis]

Well, yeah, you can do that quite easily - if it doesn't have too look pretty, that is.

Basically, what you want to do is create a table with three columns, one for the Lap Times, one for the Name and one for the difference to the first (or preceding) place.

You'll want to format the column for lap times and time differences like this (select the column, right click, select Format Cells):

For the time difference, it's best to use a formula like this:

This'll just give you the difference between the fastest lap and the one in the corresponding row.

You can always sort lap times by selecting the row in question and using the Sorting function Excel offers by default (don't know about OpenOffice).

I'd just create a simple spreadsheet for you but I only have access to Excel 2013 and I don't know whether that'll work properly with OpenOffice

 

[TB]

I'd do something like this:

That way, all you need to enter is the driver's name and time and it will sort the results.

If you'd like it to then sort based on the rank (like on a different page), that could be done with a VLOOKUP, but the columns would have to be rearanged. But I won't go into that one yet.

 

[eran0004]

I'd do something like this:

That way, all you need to enter is the driver's name and time and it will sort the results.

If you'd like it to then sort based on the rank (like on a different page), that could be done with a VLOOKUP, but the columns would have to be rearanged. But I won't go into that one yet.

You can easily sort it by making a table (Excel 2007).

In fact, you don't even need the 3rd column if you do it that way, because you can simply sort the table by time:

 

[YukinoSuzuka]

You can easily sort it by making a table (Excel 2007).

View attachment 451679

In fact, you don't even need the 3rd column if you do it that way, because you can simply sort the table by time:

View attachment 451683

Very usefull answers.. thank you all !!

What I didn't knew, is that I use OpenOffice spreadsheet.. In most of the cases, this spreadsheet is a good alternative for windows excel, with most of the formulas are just the same, but the more detailed formulas, like time, differences and all other things I don't want to know.. It's an another programm. I downloaded a free trial of excel know and hopefully I can make my table in excel and export it succesfully to OpenOffice later, because I don't want to pay for something I don't use that often

 

[Luminis]

If all else fails, you could always use Google Docs. It might be a bit finicky with the formats and stuff, but it does have the advantages of being free and online. As such, it can be shared with whomever you need it to share with, which might make things easier on your end, as far as hosting the time trial is concerned.

 

[eran0004]

If all else fails, you could always use Google Docs. It might be a bit finicky with the formats and stuff, but it does have the advantages of being free and online. As such, it can be shared with whomever you need it to share with, which might make things easier on your end, as far as hosting the time trial is concerned.

Google spreadsheets are nice, but it doesn't handle time formats very well. I played around with it yesterday and it kept changing my lap times so that they indicated dates instead. A lap time of, say 7:11.942 became a lovely January day in 1899.

I don't know about OpenOffice, but LibreOffice is just as good as Excel at counting with milliseconds, but unfortunately there's no table function similar to Excel's, so LibreOffice requires a bit of manual labour to sort and expand the data.

 

[Rallywagon]

Google spreadsheets are nice, but it doesn't handle time formats very well. I played around with it yesterday and it kept changing my lap times so that they indicated dates instead. A lap time of, say 7:11.942 became a lovely January day in 1899.

I don't know about OpenOffice, but LibreOffice is just as good as Excel at counting with milliseconds, but unfortunately there's no table function similar to Excel's, so LibreOffice requires a bit of manual labour to sort and expand the data.

You need to change the cells format. Change it to plain text. You can do this by clicking the top column cell (a, b, c etc.), high lighting the entire column, then right click on a cell, click format cell. You should find the setting in there to change the way it handles data in the cell, plain text should work.

 

[eran0004]

You need to change the cells format. Change it to plain text. You can do this by clicking the top column cell (a, b, c etc.), high lighting the entire column, then right click on a cell, click format cell. You should find the setting in there to change the way it handles data in the cell, plain text should work.

You can't calculate text though.

 

[Rallywagon]

I don't think it sees it as "text" persay, just that it will take the number in any format you give it without altering it.

 

[eran0004]

I don't think it sees it as "text" persay, just that it will take the number in any format you give it without altering it.

It treats it like a string (string of characters) and if you try to do any maths operations with it you get a value error. So although it displays properly you can't do anything useful with it.

For instance, if you have two time trial stages and you want to display the total time for a driver, you should be able to add the times for stage 1 and stage 2, and the sum is the total time. You can do that with Excel and LibreOffice, but not* with a Google spreadsheet.

(* well you can, but it's a lot more complicated.)