This is a small simple spreadsheet that is based off the excellent work of Anthony Turner, who’s videos I came across on YouTube about assessing the reliability of your data and setting targets using the smallest worthwhile change. I would encourage everyone to watch these videos for a more in depth description of these methods than I will mention here.
This is a fairly straightforward spreadsheet once again, most of your data will be input manually from your testing data, then a couple of simple equations and you can assess how reliable your data is, what the smallest worthwhile change is, and what are appropriate individual targets to set for your next testing block.
Firstly, you will input your results for each test. I have some hypothetical 5m, 10m and 20m splits for my speed tests here. Then you will set up a column in each test to look for the best score for each individual in each split. For speed tests you obviously want the lowest score possible, so your best score formula will be the minimum of the three data sets. For the best 5m split from Athlete 1 the formula is:
=MIN(B10:D10)
You will continue this process for all the splits. (You can have this equation set up to go before you input your test data if you wish, that way it will spit out the best score as you enter the data in). Then you will look to calculate the team average from the best scores, as well as the standard deviation from the best scores in each time split.
Before I go any further, I will look to test the reliability of the entire 20m sprint test for the squad. To achieve this, I will simply look to calculate the team average for each of the 20m splits, and then represent it on a graph. This graph gives me a nice visualisation of the data and I’ve made a note above the graph of what to look out for.
If the average scores for the team get better after each trial run, then you could infer that the athletes were not warmed up properly before the first test, or that they weren’t familiar with the test before the first run. If you notice this trend as the test in in progress, you could give athletes a 4th trial run to see if they improve again, if you have the time.
Similarly, if the average scores decrease after each trial, you may need to look at your rest periods between each trial.
From the data below you can see the averages for my five athletes vary, you could imagine that there was a drop in motivation for the second trial and then a last push for the final run, but overall I am happy that the data and testing protocol was reliable.
Next we need to calculate the standard deviation for the teams best scores, which we will need to calculate the smallest worthwhile change. This is shown in the picture below.
=STDEV(E10:E14)
Coefficient of Variation
Next we need to calculate the coefficient of variation (CV). The CV is a useful statistic for comparing the degree of variation in each athletes scores in a certain test.We will look to represent this as a percentage in variation. The equation for Athlete 1’s 5m trial is as follows:
=STDEV.P(B10:D10)/AVERAGE(B10:D10)*100
If we apply this formula in a CV column for all three trials for each athlete, you can see in the picture below we get a variation between the three trials in each time split, this figure is a percentage, hence multiplying the STDEV.P and AVERAGE by 100.
So for Athlete 1, they have a 0.68% variation in their 5m splits, a 0.62% variation in their 10m splits, and a 0.37% variation in their 20m splits. Ideally, I would like this % in variation to be below 5% for each athlete in each split.
We then need to calculate the team average coefficient of variation. This is a straightforward formula. You can see above the average CV for the team in the 5m split is 2.48%, which means that the error between each score is plus or minus 2.48%.
=AVERAGE(F10:F14)
Once we apply these formulas across all splits, we can then start calculating the smallest worthwhile change. The targets we set for the next test need to be realistic and achievable, yet fall outside the error of the test. The smallest worthwhile change has been described by Hopkins 2004 as the team standard deviation multiplied by 0.2. We will apply this formula to each of the time splits.
As you can see from the picture below, the smallest worthwhile change for each of the splits (5m,10m & 20m) is 0.01, 0.02 and 0.03 seconds respectively. This is an incredibly small change, that may not be detectable with the equipment we use to test sprint times.
We also need to find out if the smallest worthwhile change falls outside of the error of the test. We know that the CV is 2.48%, but we need to figure out what this is in milliseconds. To achieve this, we will divide the team average by 100 and multiply by the CV.
=E15/100*F15
The picture below illustrates a CV time of 0.03 seconds for the 5m split. So the smallest worthwhile change doesn’t even fit outside the error of the test.
The smaller our CV is, the better our tests are at detecting change.
Since we are going to retest athletes, meaning and extra set of tests, then we need to double the error to allow for error in the second test, thus, we multiply the CV by 2.
This gives us an amount of seconds, by which the athlete must improve their previous best score by, so that we can confidently say that real change has occurred (this could also be a decrease by the same amount).
Once we apply these formulas across the three splits, we can then assess whether or not these are realistic short term targets. We can achieve this by setting up a targets table for each athlete and compare the smallest worthwhile change, coefficient of variation, 2 x the coefficient of variation and a percentage of your choice. The percentage of your choice comes from being a coach, and understanding what is an achievable for each athlete.
You can take the athletes best score and take the figures from the smallest worthwhile change, coefficient of variation, 2 x the coefficient of variation and a percentage of your choice, and come up with a choice of targets to choose from.
Creating your own percentage will give you the option of setting a target that you could consider relatively reliable, yet also achievable, as the 2xCV target may be unrealistic.
You can see above a 2.5% increase in performance may be appropriate targets for the 5 and 10 metre splits, but would be too high of a target for a 20m split, so I set a 2% increase. These are compromised targets, as I feel the 2xCV score is slightly unrealistic to achieve short term, so I set a percentage increase that is achievable and relatively reliable.
Dr. Adam Sullivan 