The spreadsheets for analysis of controlled trials, crossovers and time series at the Sportscience site are based on reducing repeated measurement on each subject to a single effect (usually a change score). The resulting analyses are as effective as mixed modeling for mean effects and individual responses. When I added a covariate to the spreadsheets 11 years ago (Hopkins, 2006a) to allow adjustment for a modifying subject characteristic, I acknowledged in the article that "extending the analysis to two or more covariates is simple in theory, but it is practically impossible in Excel because of the bizarre awkwardness of the LINEST function (which performs the necessary multiple linear regression)." I have now solved this problem by getting the user of the spreadsheet to specify only one effect at a time, using a row of weighting factors to combine the repeated measurements into a single "custom" effect. The only other challenge in updating the spreadsheets was to derive the custom effect's standard error, which is needed for confidence limits and magnitude-based inferences. This challenge is easiest to understand with the crossover spreadsheet. Inclusion of covariates with a crossover is simply a matter of fitting a multiple linear regression, in which the covariates predict the individual values of the custom effect. The value of the effect predicted at the means or some other chosen values of the covariates is the adjusted effect–that is, the effect adjusted to the mean values of the covariates–and hence the need for the standard error of a predicted value in a multiple linear regression. LINEST provides standard errors only for the regression coefficients, not for any linear combination of the regression coefficients. After a fruitless search on the Internet, I worked out the appropriate formula from first principles. The formula takes into account the correlation between the two predictors to reduce (for a positive correlation) the standard error given by the sum of the squares of the standard errors for the contributions of each predictor. You can find the formula in the cell beneath the label "SE pred" in each spreadsheet. For the controlled trial and pre-post crossover spreadsheets, the standard errors of the adjusted custom effects in the two groups are combined as in the previous versions of the spreadsheets: using the Satterthwaite (1946) approximation for degrees of freedom (controlled trials), and using the correlation between the custom effects (pre-post crossover). Accuracy of the computations in the spreadsheets was checked by analysing the simulated datasets shown there with mixed modeling in the University Edition of the Statistical Analysis System (SAS Studio). The programs can be found in the suite of materials for the mixed-model workshop (Hopkins, 2016). The old spreadsheets can be accessed from the links in the 2006 article. The new spreadsheets have the following new or improved features… •
•
•
•
•
•
•
•
•
•
•
•
•
It is inevitable that the new spreadsheets have bugs, hopefully only minor formatting errors or ambiguous instructions. Please get back to me by email if you encounter problems. Also get back to me with any other comments. The new approach with LINEST lends itself to adding another covariate, which I will do if these spreadsheets are received with enthusiasm. Batterham AM, Hopkins WG (2005). A decision tree for controlled trials. Sportscience 9, 33-39 Hopkins WG (2016). SAS (and R) for mixed models. Sportscience 20, iii Published March 2017 |