I need to score a prediction run. Placing is determined by the difference between an athletes time predicted and actual finish time -- plus or minus does not matter.
Suggestions on how to do it with existing software?
Still trying to solve this one ...
Have a spread sheet going ...
In five columns have the following:
Bib No/FirstName/LastName/Minutes Predicted/Seconds Predicted
Let's assume I have these in columns
N1, O1, P1, Q1, R1 ... this is my input information
Now I have a set of columns
Bib No/Name/MinutesPredict/SecondsPredict/MinutesActual/Seconds/Actual/ABS(DIFFERENCE)
Lets assume these are in column D1/D2/D3/D4/D5/D6/D7/D8/D9
By typing a bib number in D1, I can pull the contents of O and P columns for the matching bib number into name, and fill the two predicted times using the indirect() function in excel.
D2=INDIRECT("$0"&D1)&" "&INDIRECT("$P"&D1)
D3==INDIRECT("$Q"&$D1)
D4==INDIRECT("$R"&$D1)
And then by putting actual times in columns 5 and 6, I can calculate absolute value thusly:
D7=ABS(TIME(0,D3,D4)-TIME(0,D5,D6))
Seems to work just fine ... but how to sort and rank since place order is different than rank order.
Don, does this file do what you are looking for?
Excel File
I did change the format of the left side...
Also, I think your current formulas only work if the bib number corresponds with the row number. Let me know if you want me to help with making them dynamic (so bib numbers could be anything).
Yes, that advances the ball a ton. I had some ideas for the row number issue.
I have a new version that goes another few steps ... how do I upload?
You can go to your profile ('User' - in the forums), click "Files" on the right hand side, and upload a file. You'll then just paste the link here. (Not the tightest integration, sorry!)
Only files with the following extensions are allowed: zip, gif, jpg, jpeg, png, bmp, txt, xml. Please select a valid file.
Hate to go around the system ... emailed you direct I suspect earlier.5554.Prediction Run v3.xls
So that seems to work just fine ...
I solved the following:
1. autonumbered bibs from start number in N2.
2. created an opening page that explains the what's up a little -- very little
3. managed I think to lock down all but the three columns I edit on the main page
4. should have hidden the columns I don't need I suppose on the "roster" sheet
5. added non-unique warning to the bib number entry (not the fancy red you use on emmXC, but I'm trying here ...
7. Problem: try entering times ... it goes straight down the column vs left to right ... maybe not a bad thing ... I am tempted to copy the minutes anyway and just type the seconds ... but why is it doing this.
I'm not all that sharp on excel and never would have figured out this much without your jump start.
Last question ... if I auto download my times they will be in this format:
1,1053.561,1082.571,1083.07
Quick math on the first one:
1053.56/60=17 R.559....55933333*60=33.5599998
Or 17:33.56 and so forth.
What is that you ask ... well it is Lane 1 (sprint 8 has 8 lanes), a comma, and the seconds ... this is something that needs to be solved in the emmXC program in any event by the way if it isn't already so that I can import times (and hopefully if we make progress on the 11th many others as well -- sprint8's are very very common around here).
So my actual times will need to be calculated from that AND I'd like to calculate to the hundredth if possible (I know I ripped off the time function from a friend and it's only mm:ss or some such ...
Thanks for the help you have lent me on this one!
The default behavior is for Excel to move down the column when hitting enter.
You can change the direction to go left to right (at least in Excel 2007), but I find that usually I want to move down a column of data.
If you want, you can store the minutes and seconds in the same column (I recommend the "text" format to do so).
I'm attaching an example with how I convert from times to seconds, and then back to the time (to allow users to enter times in the standard format, and then be able to rank them).
You could do the conversion from Sprint8 to time format all in one formula, if you wanted to, obviously. I just broke it out to make it easier to understand what is going on.
Time Examples
Does this help?
I work with my times in text all the time in excel. I didn't send it to you, but the reason I am where I am is a friend gave me his "prediction run" spread sheet but it was for a very small race. I am trying to make it solid enough to handle a race of 300-400 that I am doing next week. He just happened to handle the time "formatting" by splitting it, which many people do around here.
If I am to do manual entry I will probably make my input field be ###### for MMSSHH so I need not type colons or periods. However I can also reduce keystrokes considerably if I copy down the minutes and then only type seconds -- so in my head there is a debate raging.
None of that matters if I use a timer download ... which adds to the mental debate :-)
I am currently having trouble getting a sprint8 timer to download to text file on vista computer using the asciiwin program offered at timetechusa -- which seems to be a common problem. It can be done because hytek on that computer downloads times just fine. Of course I suppose I can fire up hytek and do the download ... but other friends of mine are having the same download failure issue, so I would like to fix that, plus it may be necessary to use emmXC.
Need to figure out import of times in emmXC next. Is there a help section on that?
The EMMxc (hmm, I think I might like your standard of emmXC better) has no timer import module, or download mechanism...but you can paste a column of times in. They will need to be in either MM.SS.SS or MM:SS.SS format.
Let me know if this doesn't work out.
I've been thinking about ways to import the Sprint8 times (beyond copy/paste), but I'm not sure if it would be necessary? Although probably most users aren't as savvy with data manipulation as you are, huh?
interesting ... cell b20 ...
replace:
=TRUNC(A20/60,0)&":"&IF((TRUNC(MOD(A20,60),2))<10,"0"&TRUNC(MOD(A20,60),2),(ROUNDUP(MOD(A20,60),2)))
with:
=TRUNC(A20/60,0)&":"&IF((TRUNC(MOD(A20,60),2))<10,"0"&TRUNC(MOD(A20,60),2),(ROUNDUP(MOD(A20,60),1)))
and the cell shows the little error (small green triangle in upper left corner ...) why?
I do think import will be necessary to really get good traction. Sydex is the commonly used program in our area and it has a built in timer (on the space bar). However there is no tape backup and we had at least one race in our league where all times were lost. Hytek imports smoothly from the sprint8 and most other "button timers".
I think it should be on your goal list.