This post is a Do-It-Yourself Anaerobic Speed Reserve (ASR) spreadsheet tutorial. If you don’t know what the ASR curve is, then please see my posts I take it back…Weyand was right and Bolt can run 42.80 and Dr. Peter Wayand’s study High-speed running performance: a new approach to assessment and prediction.
I’m not the best Excel guy, so I’m sure my approach can be greatly improved. But, my spreadsheet works and it doesn’t use any tricky Macros, so I think it will be very useful to you.
Now, a quick tutorial on the ASR equation before getting into Excel DIY. The basic ASR equation is as follows:
You won’t find this exact equation in Weyand paper. I’ve done some basic variable replacements to make it, in my mind, easier to read. The closest version of my derivitative can be found on page 8 (1961), equation 7.
The equation states that the maintainable maximum speed for a specified time (Spd(t)) of an athlete is equal to that athlete’s maintainable Aerobic Speed (Spdaer) plus that athlete’s exponentially decayed Anaerobic Speed Reserve (Spdasr). I know, that’s a huge mouthful. Feel free to completely forget it!
The import thing to note is that an athlete’s Spdaer and Spdasr are unchanging from one sprint to the next. They change as the athlete improves, but the changes aren’t drastic from one day to the next. Thus, doing time trials every few weeks is sufficient for generating the athlete’s ASR curve and target times. What’s also important is because these values don’t change, you can mathematically find their current values by testing an athlete with only 2 different time trials. I personally like using 30m fly and 320m fly.
If you noticed, the equations uses speeds and times, not distances. As coaches, we typically work in a world of distances and times. With the magic of Excel, we can create a continuum of speed/time/distance relationships…and that’s exactly what my spreadsheet does. Here’s a screenshot of my spreadsheet (click it to see full size).
The top left (A1:D4) is for entering your time trial information. This screenshot displays an athlete who ran his flying 30m in 3.28 and his 320 in 42.50 (using Freelap timing system). The only calculated cells in this area of the spreadsheet are the two Speed values. Cell D3 is simply “=B3/C3“. Similarly, for D4 the equation is “=B4/C4“.
The area below the two time trails is where Aerobic Speed and ASR are calculated (B7:C10). Cell C7 is just the difference between the two speeds, or “=D3-D4”.
The difference between the exponential is a little trickier, but this is Excel and it can easily handle exponentials. The equation in C8 is “=EXP(-0.013*C3) – EXP(-0.013*C4)”.
Aerobic Speed is simply the division of these two values. So, C9 is “=C7/C8“.
Now that we have Aerobic Speed, we can get Anaerobic Speed Reserve by plugging our time trail values into the ASR calculation. So, C10 is “=D3-($C$9*EXP(-0.013*C3))”.
At this point, we have all the information we need in order to create a continuum of Time, Speed and Distance projections (F8:H1000x).
Column F is the for a series of times. I decided to start from 1.0 second and add 0.1 second for each successive row. So, I entered 1.0 into cell F9, then F10 is “=F9+0.1”. At this point, you want to use Excel’s Fill Down feature. You shift-click cells from F10 down to the final row you want to fill. F999 is a good finishing point, since this exactly 100 seconds. To Fill Down, click Ctrl-D (you will use Fill Down for all remaining columns).
Now, you need to get the continuum of speeds and distances. In G9 enter “=$C$10+(($C$9)*EXP(-0.013*F9))”. That is the true ASR formula in action. It calculates the estimate speed for the given time using Aerobic Speed and Anaerobic Speed Reserve. Use Fill Down to populate G10 through G999. To calculate the distance column, simply multiple Time and Speed: H9 use “=G9*F9”. Use Fill Down again for H10 through H999.
You’ve done it!!! You now have the full curve of estimated peak performance based on the two time trials. To find the desired estimated time, scroll down your spreadsheet to the target distance and look in the Time column.
Here’s are two snapshots from this athlete’s projections. I just pick the row closest to the distance I’m interested in.
200m projection: ~24.5 (200.17 is closest to 200m)
400m projection: ~56.0 (399.73 is closest to 400m)
According to Weyand’s study, the projections are 97% accurate. Thus, I also have added columns for +3% and -3% speed, which lets me see the range in which the athlete may actually run (L8:P999). Also, I added two columns for 95% speed and 95% distance for quick reference for the various speed workouts that are to be run at 95%. You could do similar columns for 60%, 70%, 80%…whatever. I won’t go into detail on how to do this…I’m hoping by now you can do it yourself.
Now, how do you actual use this for a workout. Well, let’s assume you want this athlete to do 6 x 200m at 85% with 4min rest. In the above, we know the athlete’s target 100% 200m time is 24.5. To find 85% target time, we divide time by 85% or 0.85 (this is the same as multiple 85% by speed and then extrapolating time): 24.5 / .85 = 28.82. Viola, you have your workout!!!
Future improvements to this spreadsheet would be to pull out common distances and put their projected times at the top of the spreadsheet. Then you don’t have to go searching for them. But for now, this is what you get.
Sprint 42 
You can share the “steps” for do my own sheet, or found the way.
Thanks for awesome job you doing in this blog
Pingback: Standardizing Sprint Workout Syntax « Sprint 42
So, I did my research this year and after being messed about by a few ‘expert’ coaches I contacted Barry who set me up with his ASR and Strength system. I was very dubious at first, but I did tons of reading and I kind of got where he was coming from. I am a masters sprinter who gave up in 1998, had a few years off and decided to make a come back in 2012. I am now 43. I followed his program for a few weeks and my deadlifts went up and asr times came down. I raced all summer without injury and ended up losing 3% body fat and 3kgs. My lifts went from 80kgs to 140kgs and my 100 peaked at 12.51 and my 200m at 25.5. This is with 8 weeks training! I am looking at building my own asr spreadsheet, so thank you for the great article.
Hi coach- I am looking for information on what distances to run during the track season for my sprinters. Do you run short distances at the beginning of the season (10m,20m,30m) and progress to longer distances at the end of the season (40m 50m 60m) or would you train long to short? Do the distances change during the week -say, long to short or short to long. What is the best formula with different distances for a track season. Do you have any suggestions. Thanks-Coach Barr
In theory, yes. The day-to-day doesn’t always work out that way. But in general, I plan for 2 speed days a week and 2 fatigue days a week. Here’s what I’m doing now:
M: Accelerations, Plyos, Weights
T: Fatigue, General Strength, Core
W: active rest, like technique work
Th: Max Velocity, Plyos, Weights
F: Fatigue, General Strength, Core
S/S: active rest, like technique work
I mostly work with decathletes/heptathletes, so we are also on the track doing technique work with plays into it too. So, it’s not so simple as the above, but the above is a good place to start.
Per how the season progresses, yes, the speed and fatigue days change as the season progresses…in an outside-in model. So, the speed days get longer and most intense, and the fatigue days get shorter and more intense. At some point, they start to collide and recovery becomes more of an issues (intense speed day followed by intense fatigue day came be tough).
Here’s some example workouts and their progressions. I also coach jumpers. With jumpers, I keep the fatigue days on the shorter side…but keeping the test down so they are taxing their bio-chemical system.
Speed Days Examples (intensity low-to-high)
Hills (20-30m)
Sleds (10-20% BW)
Short Blocks 10m, 20m, 30m…
Sleds (<=10% BW)
Run-Float-Run
Long Blocks 60m
Flys
Fatigue Days (intensity low-to-high)
10x100m(400m)[60"]
8-12x150m(400m)[2']
6-10x200m(400m)[2']
2×500 (15m) + 2×200 (2m)
Long Hills (100m+)[walk back]
3 x 200m(85%, 90%, 95%)[walk 200] aka 200m Step-Down (each rep is 1 second faster than previous)
3 x 150m (95%)[15']
Hope this helps
Nice job on the spreadsheet but I found this method to give highly inaccurate results. I used my best 20 and 400 meter times and the predicted 200 meter time was almost 14% slower than my best 200. I then tried 20 and 300 meter times and it was still about 9% slow. Don’t see how this is useful.
Are you using fly times? You can’t use times from blocks as that will skew the data considerably. Post your data if you’d like me to take a look.
I’m curious about some results I’m getting and was wondering if anyone else has had something similar come up. I have two guys with the following stats:
Athlete A – 40m Fly – 3.94, 300m – 34.7
Athlete B – 40m Fly – 4.11, 300m – 35.42
When I use the calculator to predict the 800m times, Athlete A has a time of 121.79 and athlete B has a time of 117.23. How is Athlete B predicted to run a faster 800m than athlete A, when Athlete A has both a faster 40 and 300m time?
(it only took three years to reply) — At first glance, I would expect athlete B to have the better 800m because he is clearly more aerobic. The speed curve is an exponential decay curve flattening out around the athlete aerobic threshold. An athlete like Athlete B is going to be much more aerobic than Athlete A, which clearly plays a more important role in the 800m, but not in the 400m. If we drew the two curves out, I would expect Athlete A’s curve to drop under Athlete B somewhere between 400m and 800m. Athlete B would likely be stronger at everything 800m and above (it would be a great 600m race between the two of them).