Excel spreadsheet that calculates split times for track?

thewanderer wrote:

I am looking for an Excel spreadsheet that will calculate my distance runners' individual 400 splits once I enter their elapsed time. I have tried a Google search to no avail. Does anyone have such a document? Thanks!

This is a ridiculously simple function. I would encourage you to explore Excel to solve this yourself. You'll learn something and get some satisfaction.

Ignore the idiot who refuses to answer a simple question, like he's your father and is trying to teach you a life lesson. Thanks, Dad, I'll go out and chop that wood now.

If you enter the 1600 time as mm:ss, then just divide by four:

Cell A1: let's say you enter 5:20

Cell A2: formula is A1/4

And of course you can divide by 8 if you're entering a 3200 time.

Of course, this just gives you the average split. Obviously, only you know the actual splits.

thatisdumb8 wrote:

Ignore the idiot who refuses to answer a simple question, like he's your father and is trying to teach you a life lesson. Thanks, Dad, I'll go out and chop that wood now.

If you enter the 1600 time as mm:ss, then just divide by four:

Cell A1: let's say you enter 5:20

Cell A2: formula is A1/4

And of course you can divide by 8 if you're entering a 3200 time.

Of course, this just gives you the average split. Obviously, only you know the actual splits.

If you entered in each of the total elapsed split times in column A (A1, A2, A3, A4,...) then to get each split you just have to subtract one number from the one above it to get the difference.

Doesn't all this presume your runners are doing even splits? I would think manually tracking each lap would yield more useful data.

thewanderer wrote:I am looking for an Excel spreadsheet that will calculate my distance runners' individual 400 splits once I enter their elapsed time.

You can use Excel like the others have suggested to calculate the average splits, but if you want to know their actual splits (which should be far more meaningful than average, to my way of thinking) you need to record (or have them record) the actual splits. Unless they are running dead even pace (not likely) the average splits won't be of much obvious use.

That said, set up the cell to have the numbers in time format (H:M:S or the like) and then just divide the total elapsed time by the number of laps, which can be fractional (12.5 for 5000, or 3.75 for 1500 as examples).

Whats wrong with using this? It gives you splits anywhere from 1 yard to 1 mile. And its the top result when you search for pace calculator. http://www.coolrunning.com/engine/4/4_1/96.shtml

Yes I want actual splits, not average. In other words, I want to enter their splits that they run in the meet (elapsed time) and have the spreadsheet calculate the actual time for each lap. Just trying to save time, I have a lot of runners, and I'm not an Excel guru by any means. Just thought there might something out there already. Thanks to those who helped.

thewanderer wrote:

Yes I want actual splits, not average. In other words, I want to enter their splits that they run in the meet (elapsed time) and have the spreadsheet calculate the actual time for each lap. Just trying to save time, I have a lot of runners, and I'm not an Excel guru by any means. Just thought there might something out there already. Thanks to those who helped.

So you're trying to use subtraction?

= time at mile 2 - time at mile 1 should give you the split, right?

Subtraction yes but for each 400. Looking to calculate splits at 400, 800, 1200, 1600, 2000, 2400, 2800, 3200. In other words if I enter in elapsed splits of 1:11, 2:30, 3:47, 5:10, 6:30, 7:51, 9:20, 10:33 I want the Excel sheet to calculate each 400. I can do that by hand, which is what I have been doing. Like I said, a lot of runners, just trying to save time. I preach about splits a lot. The above example would not be good splits, obviously.

You want to have two columns for each runner. One column with the elapsed splits, then another subtracting the elapsed times to produce the split automatically.

First select all the columns that will contain times and right-click > Format Cells.

Select Time > 30:55.2

Next enter in the elapsed splits in successive cells.

Elaps

Lap 1

Lap 2

Lap 3

Lap 4

Then in the cell to the right of each lap enter the split:

Elaps | Split

Lap 1 | Lap 1

Lap 2 | Lap 2 - Lap 1

Lap 3 | Lap 3 - Lap 2

Lap 4 | Lap 4 - Lap 3

Then grab that group of cells and copy them for each runner.

If you want to really make it look good, we can add conditional formatting where the slowest split background turns red, and the fastest turns green.

Personal bests can be bolded.

We can do pretty much anything you can think of.

Then if you want the power of a data God, we can move all this into a database. That's how the pros manipulate data.

Great Googly Moogly! Please tell us that you are not also instructing these kids in any sort of classroom...

TurnItUp wrote:

Great Googly Moogly! Please tell us that you are not also instructing these kids in any sort of classroom...

a lack of knowledge of excel doesn't disqualify one from being a good teacher

wanderer: i'm busy, but if you're nice i'd bet you could convince somebody to make the excel sheet for you and then email it to you. simply input your email address next time you make a post on this thread

I would label the columns "Distance", "Split Time", and "Lap Time" and under each put:

A2 thur A9 = "400m" "800m" "1200m" etc..

B2 thur B9 = The split times (1:11, 2:30, 3:47, etc)

C3 = this formula "=B5-B4"

copy and paste C3 into C4 thru C9.

You will need to format the cells B2 thru C9 to display [m]:ss.

WHOOPS! f'ed that all up.

That C3 formula should be "=B3-B2" obviously.

This is what's possible with a database:

1971-2014-Individual-Race-Results

https://docs.google.com/spreadsheets/d/1G_R7lGKO7_BVKm3WQ_ckDJIXNTyfY24JsXC8rNNlgOQ/edit?usp=sharing

1971-2014-Team-Race-Results

https://docs.google.com/a/xtremedatainc.com/spreadsheets/d/199Iso6cR7KkVU8XOYKrLFIX49lb__Kwrw83yQsSNzcs/edit?usp=sharing

I combined all the State meet performances into one database (as an exercise to learn databases), and then "ran" a virtual race among all teams to see who would win. There's a whole lot more info in there too that you can't see, the coach names, the weather on race day including wind, temp, and rain, the hometowns, etc.

The code to run the race is only several pages long. The hard work on databases is cleaning up the data and entering it, after that it's easy.

thewanderer wrote:

I am looking for an Excel spreadsheet that will calculate my distance runners' individual 400 splits !

Just make your own. I do this all the time for the different training courses my runners run. Remember to change time to base 10. Ex. 2:15 would be 2.25, 3:24 is 3.4, 4:29 is 4.4833, etc.

This isn't brain science or rocket surgery.

Try the PaceMeister. Your welcome.

Bottom of this page

https://2008olympictrialsakatommyleonard.shutterfly.com/filecabinet2

or direct download link.

https://2008olympictrialsakatommyleonard.shutterfly.com/filecabinet2/138/PaceMeister%20Improved%20ver%205.19-3.xlsx