In ANOVA Approach to ANCOVA, we show how to use Real Statistics’ ANCOVA data analysis tool when data is in Excel format. This data analysis tool can also be used with data in stacked (i.e. standard) format. In fact, for data in stacked format, the data analysis tool also provides support for Contrasts and Tukey’s HSD test, as described in ANCOVA Analysis Tools.
Real Statistics Function: The Real Statistics Resource Pack provides the following array function to convert ANCOVA data in Excel format to stacked format.
ANCOVA1Std(R1,R2): outputs an array in stacked format for the input data in array R1 and covariate data in array R2. R1 and R2 don’t contain column headings.
Example 1: Convert the data for Example 1 of Basic Concepts of ANCOVA to stacked format.
Highlight the range L3:N38, insert the array formula =Ancova1Std(B5:E15,G5:J15) and press Ctrl-Shft-Enter. If you are not sure how big to make the output, you can guess as to its size and then enlarge the range afterwards if necessary. The first 17 rows of the output is shown in Figure 1.
Note that column M contains the input data (i.e. the data for the dependent variable) and column N contains the covariate data.
Figure 1 – Conversion of ANCOVA data in Excel format to stacked format
As can be seen from Figure 6 of ANOVA Approach to ANCOVA, there is also an option to take data in Excel format and automatically convert the data to stacked format from the ANCOVA data analysis tool where it can be used to perform ANCOVA, Contrasts and/or Tukey’s HSD tests.