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.