Recording lap times by entering contestant number

What you're asking for is fairly simple.
I'm on my way out the door right now. I'll be back in an hour. If no one has answered you by then, I'll set it up for you.

Sorry it took so long. I got hung up on my errand.

Check out this file:

Enter Number of Laps and Number of Racers. The workbook will automatically add enough rows and columns to accommodate the race. Enter Racer's Name and # in the appropriate columns.
Actually I just realized that it probably can't handle more than about 100 laps if you open it in Excel 2003 or earlier. Each Lap requires 2 columns to keeps them sorted. As long as you open it in 2007 or later you should have no problem. If you need more, then let me know and I'll redo it.

Click "Clear Race" to clear out the last race.
Click "Start Race" to start the race.

Type in the Number of the Racer in "Racer Number" and hit "Enter". It will automatically return the cursor to "Racer Number", so you can just concentrate on entering the numbers correctly.

The workbook automatically sorts the Racers to show who's in the lead.

It just now occurred to me that if the race lasts more than 1 day the "Race Started" field needs to be formatted to include the date.
If you need to use the file for both 2 day races and 2 hour races, let me know and I'll put in a checkbox to indicate that it needs the date in RaceStarted.

Originally Posted by foxguy

Sorry it took so long.
...

Wow, yeah, no need to feel sorry! Thats great, thank you.

But I may have been unclear, see, the race in question is an endurance race (Enduro, it's called), which means that they have a finite time to race and the winner is the one who manages to complete the most laps in a set time frame.

So let's say the race starts at 0800 and they have four hours to race. Every time they pass the finish line their number is recorded, and if they pass the checkpoint before the time is up they get to complete a last lap (eg at 1159). The elite racers will most likely manage the same number of laps and be ranked by finish time accordingly.

And they all start simultaneously, so the start function basically just kicks the timer in motion (for everyone), and then every time a number is entered (as the numbered racer passes the checkpoint), the lap time is recorded next to his/her number.

The list of contestants will look like this (i wish I could make cells here):
Name | Number
Joe Doe | 101
Billy Kid | 102
Jay Bird | 103
Jane Doe | 201
etc

So some changes need to be made... But big ups for the response though; more than I was hoping for! If you can help me further I would like that very much!

I have read your response twice now and I don't understand why it won't it work as is?

After 4 hours, the sheet shows what lap they were on (just subtract 1 to show how many completed) and the time they took to finish those laps. It also shows how long each lap took.

The only thing is you have to put in a # of laps that is more than anyone can possibly do in 4 hours. ie. if the fastest anyone can do a lap is 3 minutes, the best anyone can do is 80 laps in 4 hours ((4hours*60 minutes)/3 minutes), so putting in 100 laps will give you what you want and you can still use the sheet for races with a fixed number of laps.

But it wouldn't be hard to set it up so that you don't need a # of laps, the workbook just keeps adding columns as someone finishes one. Do you want that option?

Also, I thought about doing this but it was late last night so I skipped it. It can be set up so you don't have to enter # of Racers, just type a name at the bottom of the list and the workbook adds all the formulas for that row.

Also it occurred to me that if you want to reduce the # of laps and/or racers from the last race, I didn't set it up to delete the extra rows and columns. Easy enough if you want.

All of this boils down to : Are you going to use this file over & over again, or only one time?

Originally Posted by foxguy

I have read your response twice now and I don't understand why it won't it work as is?

Ah, you are right, of course. I had it pictured so differently in my head I just couldn't make sense of it.

Originally Posted by foxguy

But it wouldn't be hard to set it up so that you don't need a # of laps, the workbook just keeps adding columns as someone finishes one. Do you want that option?

I can make do, as you say. Would I like it? Yes!

Originally Posted by foxguy

Also, I thought about doing this but it was late last night so I skipped it. It can be set up so you don't have to enter # of Racers, just type a name at the bottom of the list and the workbook adds all the formulas for that row.

Would that work for pasting a pre-formatted list consisting of two columns with name and number? In any case, same here; the functionality is already there, but it would be great for sure.

Originally Posted by foxguy

Also it occurred to me that if you want to reduce the # of laps and/or racers from the last race, I didn't set it up to delete the extra rows and columns. Easy enough if you want.

If the above functionality is in, this would be superfluous, correct? Otherwise it would be nice, but I'm assuming they can be deleted manually without problem?

Originally Posted by foxguy

All of this boils down to : Are you going to use this file over & over again, or only one time?

Short answer: ~5 times per year. The main use is for an annual race, and then we would use it for impromptu races in-house a few times a year.


How do I enter the racer number, though? After resetting and starting anew, nothing happens when I enter a number in the Racer Number cell. (Using the example racers you entered and entering one of their numbers, eg 40)

I have to leave for a couple of hours. I'll talk at you when I get back. I'll have to test it again. It was working fine last night.

I did find a bug/error in my code. You should have gotten an error message.
Are you sure that you have macros turned on?

I have verified that this file works.

It's working! Thanks a bunch. I think I should mark this solved now. Unless the changes you proposed are on the table?

You can mark it solved, even if we still keep it going.
What changes do you want?

Hi;

a, b, c, d, & e are a piece of cake.

I thought of # 1 myself, but I wasn't going to call your attention to it because I'm lazy and didn't want to think about it. The issue is that the workbook stores the actual Time Finished in a column, what you see on the screen is the Lap Time, so you can either see the Lap Time or Time Finished, but not both at the same time. That's why it takes 2 columns for each Racer.
Although it just now occurred to me that I can easily set it up so that you can bounce back and forth between Time Finished (where you could change or delete the Time Finished) and Lap Time (which you cannot change, it gets changed automatically when the Time Finished is changed).

# 4 & 5 are easy (a little more explanation on # 5 will be needed to get it right).

# 3 Is easy to separate them when they are copied to another sheet. It would take a bit of work to have 4 columns of "Order", but not hard.

# 2 is the bugger. It would require a complete rewrite of the whole thing, unless you just want to have a column of Times Finished somewhere with no idea at all of who they belong to and at some later time you type in a Racer # next to the Times Finished in that column. Then it's fairly easy.

I cannot guarantee that I can do all this stuff on a timely basis. I only work on stuff from the forums during my free time. Right now I have free time, but I could get another client at any time. If you look at all my posts, you will see that there's a few months gap in my postings because I picked up a client and didn't have any free time.

Let me know the priority for each change and I'll start tomorrow on the highest priority.

Here they are in order of priority with some added detail and/or suggestions. If one addition affects the others in such a way that it would be impractical to do in this order... It is obviously not the ten commandments, so to speak. I imagine A through F to be such, and since you said they were quick and easy, they are at the top. The single most important feature is probably number 1., though.

1. Set it up so that you don't need a # of laps, the workbook just keeps adding columns as someone finishes one.
   
   
2. Set up so you don't have to enter # of Racers, just type a name at the bottom of the list and the workbook adds all the formulas for that row.
   
   Would this work when pasting a pre-formatted list of all the racers? Otherwise, I think it is better the way it currently is.
   
   
3. Add a column next to the Order column with the order number. The current leader is 1, second is 2, 3, 4, 5, etc).
   
   
4. Set it up to delete the extra rows and column, if you want to reduce the # of laps and/or racers from the last race.
   
   
5. Change the Lap column to say Laps and show the number of laps Finished instead of Started.
   
   
6. Switch order of the columns Name and Number so that Number goes first.

-

1. The ability to make changes in the data while it is running. Number associated with a time, and time associated with a number. In case a wrong number is entered, or entered prematurely/late.
   
   set it up so that you can bounce back and forth between Time Finished (where you could change or delete the Time Finished) and Lap Time (which you cannot change, it gets changed automatically when the Time Finished is changed).
   Both the Time Finished and Lap Time is then shown in the same cell, depending on what you want to see? Sounds great!
   
   
2. Separating the results/standings to categories based on their starting number. We usually have four classes; 1xx (101-199) for elite, 2xx for recreational, 3xx for female contestants, 4xx for classic bikes.
   
   Is it possible to have several sheets updated in real-time from the data entered on the first sheet? It should be, I guess. If so, the main sheet could contain all racers and their associated times, while a following four sheets contain the same type of list but for each class? Being able to see the standings in each class in real-time would be great; then we can call out the current standings (and not be wrong half the time, as is the case currently )
   
   
3. (The ability to export the results/standings (name, number, lap times, total time) to separate sheets by category, for easy overview, printing and presentation.)
   
   If the functionality above (2.) is added, this certainly seems superfluous? It should then be possible to simply print that?
   
   
4. The ability to enter no number, maybe an X or something, to mark down a time and then be able to enter the correct number associated with that time-mark later.
   
   I think this is what you suggested was easily doable, and is definitely sufficient:
   If nothing (null), or an X, is entered, a Finished Time is saved to a separate list. Each new Finished Time in this separate time-list has a cell beside it where you can later enter a number. When a number is entered there, that Finished Time is then associated with that number, and entered into the main results. That entry should then be removed from its temporary location in this separate list.
   
   
5. Ability to mark a contestant as DNF (Did Not Finish) and have him moved to the bottom of the list (on the main sheet and of his class), and preferably keep any associated recorded data. The DNF racers should preferably be ordered internally by their results before being flagged DNF, but it is not important.
   
   A separate box much like the Racer Number entry box, but where if a number is entered, that number (racer) is flagged as DNF. Sounds good to me!

And I completely understand about free time. I'm grateful for anything and everything. We are not a tech-savvy bunch and this will help a lot!

I just started reading # 1 before I read the rest and have a problem with it.

Both the Time Finished and Lap Time is then shown in the same cell, depending on what you want to see? Sounds great!

I wouldn't bet on being able to see them in the same cell. I can set it so that when you click a button (or some other trigger) it puts the Time Finished in the same position that the Lap Time was, but I'm not positive you could see both the Time Finished and the Lap Time at the same time in the same cell.
If you think you want it that way, I'll have to think about for a while.

I'm guessing that the speed of the workbook can be critical. Excel works a lot faster than VBA. So using formulas is more efficient (and easier to debug) than using VBA to make calculations. There is no way in the world that the user can edit a cell and then formulas put a calculation in the same cell. It has to be VBA. So the Finished Time and Lap Time should be in separate cells. Just click a button to alternate which one is visible.

Now I'm going to read the rest.

I'm going to ask questions as they occur to me. Just 1 question per post. (I have had problems in the past putting more than one question in a post and getting answers that help me). Since you're in Sweden and I'm in USA we probably won't be on the forum at the same time, that should eliminate a lot of wasted time trying to get answers.

Ability to mark a contestant as DNF (Did Not Finish) and have him moved to the bottom of the list (on the main sheet and of his class), and preferably keep any associated recorded data. The DNF racers should preferably be ordered internally by their results before being flagged DNF, but it is not important.

A separate box much like the Racer Number entry box, but where if a number is entered, that number (racer) is flagged as DNF. Sounds good to me!

A Racer that does not finish the same # of laps as the other Racers will automatically be at the bottom of the list.
Do you want the letters "DNF" displayed next to their name, or is it sufficient that they are just at the bottom of the list?
In an endurance race, I wouldn't think that there would be any actual DNFs. I would think that just showing that they only finished 3 laps when everyone else finished 7 laps would indicate that they couldn't endure as much as the others.

The ability to enter no number, maybe an X or something, to mark down a time and then be able to enter the correct number associated with that time-mark later.

I think this is what you suggested was easily doable, and is definitely sufficient:
If nothing (null), or an X, is entered, a Finished Time is saved to a separate list. Each new Finished Time in this separate time-list has a cell beside it where you can later enter a number. When a number is entered there, that Finished Time is then associated with that number, and entered into the main results. That entry should then be removed from its temporary location in this separate list.

I can't think of any way that the workbook can tell if you typed X by mistake or not. So you may end up with 10 times needing Racer #s, but only 9 Racers finished a lap during that time.

I also can't think of any way to know what lap a Racer finished for the time in the list.

If you enter and X for Racer #40 Lap #3, the workbook only knows what time a lap was finished. It would leave Lap #3 Finish Time blank. If #40 finishes Lap #4 before you assign a Racer to the previous time, the workbook would assume that #40 just finished Lap #3.

In short, as long as there is a separate list of Times with no Racers attached to them, the workbook would probably mess things up until you got rid of the separate list of times.

The only thing I can think of at the moment is to force you to enter a Racer # and Lap # for each Racer.
1) When you enter a Racer #, the workbook enters what it believes is the correct lap and waits for you to either correct or confirm it.
2) When you try to attach a Racer to a Time in the separate list, you also have to tell it what lap it was, so the workbook could insert the Time into the correct spot in the Finished Times table.

Edit: I just realized that the separate list of times could figure out what lap it was, as long as you got the Racer # correct. If Racer # 40 finishes Lap #2 at 8:15, then you enter an orphaned time 8:30, then entered #40 at 8:30 (the workbook would think it's Lap # 3), then went back and attached #40 to the orphaned time of 8:30, the workbook could determine that it had to be Lap #3 and move all the Finished Times appropriately.

This would mess up the Order until you attached a Racer to each orphaned Time. The workbook would assume that #40 only finished 3 Laps while everyone else had finished 4 Laps. When you attached #40 to the orphaned Time, it would self correct the Order.

At this moment I think the best way would be (but I reserve the right to change my mind at a later date):

When you enter Racer #, the workbook enters what it thinks is the correct Lap and waits for you to either correct or confirm that Lap #. That requires you to hit "Enter" twice for each Racer. But it allows you to change Lap #3 to Lap #4 in my example above and leave Lap #3 blank (until you attach a Racer to the Time in the separate list). That way the order remains correct. The display of Lap Times would show "0:0:0" for Lap #3 and "0:30:00" for Lap #4 until you attach a Racer to the Time in the separate list.

Today was a rough day. Couldn't get started yet. But something occurred to me.

Is there ANY chance that this will be used in Excel 2003 or earlier? If there's any chance that it may be used in 2003 there is probably a maximum # of Laps about 100. I would want to put Finished Time and Lap Times on different sheets which would make a maximum about 200 Laps, which might get complicated. But if it will only be used in 2007 or later, then it's a moot point.

Here's a file I don't quite feel comfortable with. I feel like I have overlooked something. So test it as thoroughly as possible.

Originally Posted by Barbosa021

Hi, I really liked your file, but I noticed that it sorts by minimum time, is there a way to sort by first-come-first-served basis?

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Hi, my first time here. Looking for some help also. and somewhat my concern is similar.
I am trying to figure out how to do it for a bike race with a hundred or so contestants and with 3 stages

Column A: Racer Number
Column B: Start Time (each racer has 1 minute interval)
Column C: The lap time for the racer (using iPhone stopwatch to monitor lap time of each racer)

I wanted to compute their final race time to identify the winner per Stage (race has 3 stages)

Also, wanted to know the final winner after the 3 stages

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread