Graphing NP, IF, TSS, ATL, and CTL in MS Excel

3 10 2010

As a road racer, it’s always good to know when you’re headed into an over-trained condition. In this post I’ll write about an expedient method to track various metrics associated with training with power and knowing when to back-off. In this case, I use a CycleOps PowerTap SL+ hub as my measuring device, and PowerAgent software to provide some of the metrics. Plain old Microsoft Excel is used to graph Acute Training Load (ATL) and Cumulative Training Load (CTL).

Here’s some quick background:

Now for the metrics that are not calculated for us. By the way, if you use Cycling Peaks software, TSS, ATL, CTL, and TSB are calculated for you.

PowerAgent provides the data for the spreadsheet, and only minor input is needed. First, open the first ride file to pull metrics from (9/2/2010) and find the workout summary:

Screen shot of the Workout Summary, PowerDetail Tab

Screen shot of PowerAgent Work Tap

I then opened a file in Excel and created columns, rows, and formatting that looked like this:

Screen shot of Excel spreadsheet

Except for the first Wednesday through Sunday segment, the ATL (column) cell values are range sums of each Monday through Sunday. Thus, the contents of cell F14 is: =SUM(C8:C14). Likewise, the only CTL value in cell G32 has this content: =SUM(F2:F35). I transcribed all the ride data for which I wanted to examine. It really did not take that long. After your rows are finished, create a graph using the graph wizard. My data range did not include column “A”, or row “1”.  I added the source data as such:

Screen shot of graph wizard source data window

Note that I used the second column “Date” as the X axis labels. “Data” is the worksheet name in my workbook where my data rows reside, the chart is on a separate worksheet.

Including the metrics of NP and IF help to round-out what’s happened or what may happen:

ATL CTL Excel Graph

Here’s where it is all supposed to come together. The recovery guidelines are from Hunter Allen’s book on page 130. Remember, ATL is the short-term accumulation of TSS score training-induced fatigue (to be corrected and quantified later), for this graph I used weeks as the period. On the graph, the first blue cross of 9/6/2010 has a weekly total of 91 points. Per the guidelines, my recovery from that week of training should be complete by the morning of 9/8/2010. On the other hand, the ATL level at the end of week 9/12 is 810; recovery could take several days.

CTL accumulates over longer periods, in this example the CTL level for September is 1605. This may be the ideal level of training stimulation needed for positive, long-term  fitness adaptation. Or it may not be the ideal level. fitness and fatigue levels are affected by training stress is an area currently under study, theories about training load are on the cutting edge of exercise physiology…and the vote is still out on how it all works.

Allen states that there are many factors that affect how ATL and CTL interrelate and how they ought to be managed. Viewing and understanding the “big picture” is strongly recommended.

I this post I discussed a frugal tool to use in order to record and evaluate training efforts. I will use this method in addition to others to help me understand the causality of my particular adaptation. On the other hand, it wouldn’t be difficult to rationalize buying Cycling Peaks or another more comprehensive software application to perform the same task.

I hope this may help you discover your specificity.

Note (10/20/2010): My original interpretation of acute training load (ATL) was incorrect. I’m gaining additional understanding about the quantification of this metric and will update this post as I learn more.




11 responses

3 10 2010
Spokane Al

Wow – very impressive Eric! I have always enjoyed Excel and greatly admire those who have truly mastered its massive potential. I would definitely put you in that category.

As for myself – I took the easy way out and purchased WKO+. It does all of that stuff in a very elegant presentation with data that can be easily drilled deeply for a thorough analysis.

3 10 2010
Eric Abbott

Thanks Al!
I used to do a lot of back-office programming to include MS apps, Adobe Acrobat, and Autocad 14. It seemed natural to use Excel for this particular (quick) solution.

Best regards,

4 10 2010
bet365 italia

hello!This was a really fabulous topic!
I come from itlay, I was fortunate to look for your blog in wordpress
Also I obtain a lot in your theme really thank your very much i will come every day

16 11 2010
Chris Martin

When I look at the “work” tab for my power files in Power Agent it shows a TSS and IF of zero. Everything under the “power detail” tab is there, but nothing for “work”. Any ideas?

16 11 2010
Eric Abbott

I’m trying to duplicate your conditions on my end.

If your duration for the ride was 0, then having 0 for TSS, and IF would make sense because TSS and IF are mathematically related. OTH, your power tab contains watt and watts/kg figures meaning there was some quantity of duration.

I’d like to help. Could you export your ride data to a .csv file and email it to me?


16 11 2010
Chris Martin

OK I have the file ready to email. What is the address? You can send me one, then I’ll reply with the file.


16 11 2010
Chris Martin

I found your address. It’s on the way.

3 01 2013

would like a copy of the xls file as well as I am trying to do this myself. thanks in advance

12 12 2014

Does anyone know if there is a way to export all of the data from power agent to excel without the input?

12 12 2014
Eric Abbott

Hey Nicholas,
The only way I know how to bring PT data into Excel from PowerAgent is to export the ride file using either a CycleOps Power data .csv or CycleOps “extended” power data .csv format. Both of these options are available when you select the ride file in Activities, then click File / Export / Activity Data from the drop-down menu. The difference between the two shows when you open the new .csv file in Excel. The “extended” version will basically bring the header information into Excel as a number of rows of information, followed by the columns of your respective data-watts, heart rate, speed, cadence, etc. The “regular” .csv version just opens with your columnar data.

If your phrase “…without the manual input.” means taking the data straight from your PT CPU or Joule head unit into Excel; I have not tried to do that. Let me know if I have interpreted your phrase correctly.

Thanks for your question!

12 12 2014

*manual input

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: