Marshall from Pennsylvania watched Episode 680 from last Friday and came back with a cool use for the same technology: scoring runners in a 5K or 10K race. In Episode 685, we take a look at a simple system in Excel to track the finishing times as you type in bib numbers.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Last Friday I showed a little trick here where we could enter data in Column A and it would automatically put the date and time over in Column B.
And Marshall from Pennsylvania wrote in and said hey, you know I saw that trick I bet that could be adapted to score runners.
You know in a race basically, every time the certain runner passes the finish line we enter the bib number in column A and then we have to record the time that he passed the finish line.
He says couldn't this just be adapted.
So, let's take a look, I'm gonna go into VBA, Alt+F11 and this bit of code has to live on the worksheet.
So, we're on a worksheet here called new wanna double click that you see that we have our code from last week, Worksheet_Change and what we were doing then is, anytime that we entered something in Column 1 over in Column 2 we were entering the date and time.
Well, it's very simple to modify this we can just put in the time exact same code as before and let's give it a try here.
So, basically you start the race maybe, you even note that you know that the race started now, so we'll put in current time with them control shift semicolon, format that because they're out running right now we can screw around with the spreadsheet.
I'll go into time and show it with seconds.
All right, so there's our time we started 7:27 and basically, we let the runners go out and run the race you know maybe 10 minutes later.
We come back and then when the first runner passes the finish line.
All right, so you know here's runner number 52 we enter that data, press Enter and what we get over there in Column B, is that it was 7:27:44 and now we kind of wait for the second got it to come across the finish line.
You know maybe that was runner number 5 and there is his time.
Now, the only problem you're gonna have is the groups of runners.
So, you get a whole bunch of groups of runners coming all basically at the same time.
You know someone has to call those out to you. It would be tough to do on a laptop, you would want to have a desktop were we had a numeric keypad.
You know, but you could basically so, there's run or 11,12,14 ,54 all coming in with a few seconds of each other.
You know that's the only downside of this.
I've seen great systems for keeping track of this that have the little optical eye and the tear-off tags, and it's brilliant if you don't want to pay for the one of those expensive systems you could very quickly do something with a laptop in Excel and it would work fairly well.
Of course now, over here in Column C. Once the race is done we would want to calculate the difference between the person's finishing time and the original time I'm going to hit F4 there to make sure that D1 has dollar signs before the D and before the 1.
Will probably want to format that.
So, that way it just shows us the hours, minutes and seconds or maybe just the minutes and seconds, Click OK and we'll copy that down to all of our finishers, and we'll see how long it took for each one of them.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Last Friday I showed a little trick here where we could enter data in Column A and it would automatically put the date and time over in Column B.
And Marshall from Pennsylvania wrote in and said hey, you know I saw that trick I bet that could be adapted to score runners.
You know in a race basically, every time the certain runner passes the finish line we enter the bib number in column A and then we have to record the time that he passed the finish line.
He says couldn't this just be adapted.
So, let's take a look, I'm gonna go into VBA, Alt+F11 and this bit of code has to live on the worksheet.
So, we're on a worksheet here called new wanna double click that you see that we have our code from last week, Worksheet_Change and what we were doing then is, anytime that we entered something in Column 1 over in Column 2 we were entering the date and time.
Well, it's very simple to modify this we can just put in the time exact same code as before and let's give it a try here.
So, basically you start the race maybe, you even note that you know that the race started now, so we'll put in current time with them control shift semicolon, format that because they're out running right now we can screw around with the spreadsheet.
I'll go into time and show it with seconds.
All right, so there's our time we started 7:27 and basically, we let the runners go out and run the race you know maybe 10 minutes later.
We come back and then when the first runner passes the finish line.
All right, so you know here's runner number 52 we enter that data, press Enter and what we get over there in Column B, is that it was 7:27:44 and now we kind of wait for the second got it to come across the finish line.
You know maybe that was runner number 5 and there is his time.
Now, the only problem you're gonna have is the groups of runners.
So, you get a whole bunch of groups of runners coming all basically at the same time.
You know someone has to call those out to you. It would be tough to do on a laptop, you would want to have a desktop were we had a numeric keypad.
You know, but you could basically so, there's run or 11,12,14 ,54 all coming in with a few seconds of each other.
You know that's the only downside of this.
I've seen great systems for keeping track of this that have the little optical eye and the tear-off tags, and it's brilliant if you don't want to pay for the one of those expensive systems you could very quickly do something with a laptop in Excel and it would work fairly well.
Of course now, over here in Column C. Once the race is done we would want to calculate the difference between the person's finishing time and the original time I'm going to hit F4 there to make sure that D1 has dollar signs before the D and before the 1.
Will probably want to format that.
So, that way it just shows us the hours, minutes and seconds or maybe just the minutes and seconds, Click OK and we'll copy that down to all of our finishers, and we'll see how long it took for each one of them.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
