How to Perform an ANCOVA Test in Excel?

When a third variable (referred to as the covariate) is present that can be measured but not controlled and has a clear impact on the variable of interest, analysis of covariance (ANCOVA) is a technique used to compare data sets that contain two variables (treatment and effect, with the effect variable, referred to as the variate). ANOVA with covariance is a broad linear model that combines ANOVA with regression. The ANCOVA test determines if the means of a dependent variable are the same across levels of a categorical independent variable. An ANCOVA (“analysis of covariance”) is used to assess whether or not there is a statistically significant difference between the means of three or more independent groups after adjusting for one or more factors.

Step 1: Input Your data on an Excel sheet

Enter your data into Excel, ensuring that you have one dependent variable (Y), one independent variable (X), and one covariate (C).In Excel, arrange your data in a tabular format, with the dependent variable in one column, the independent variable in another, and the covariate (control variable) in yet another.
. To begin, enter the data in the following format:

Step 2: Calculate the variance  and mean  for each and every  column

The average of a group of numbers is called the mean. Add up all the numbers in the set, divide by the total number of items in the collection, and that is mean. Calculate the mean and variance for each column To determine the mean and variance for each column in an ANCOVA, you must first define which columns are being considered.:

Mean = (sum of all values) / (number of values)

Variance = (sum of (value – mean)²) / (number of values – 1)

Step 3: Determine the regression lines’ slopes

The slopes of the regression lines of the exam scores will then be calculated for each studying strategy. To calculate the slope of a regression line, divide the variance of y values by the standard deviation of x values and multiply by the correlation between x and y.The slope of a line indicates how steep it is. You need two points on the line in order to compute the slope of the line. To get the slope, use the following formula:

Slope = (y₂– y₁) / (x₂ – x₁)

Step 4:  Perform a one-way ANOVA on Exam Scores

Calculate the adjusted means for each group using the regression equation. To accomplish this, enter the mean values of the independent variable and covariate for each group into the regression equation and solve for the predicted value of the dependent variable. Create a new column for these adjusted means.
The exam scores will  be subjected to a one-way ANOVA:

Step 5: On the present grades, run a one-way ANOVA

This is accomplished by selecting Data Analysis > ANOVA: Single Factor, using the dependent variable as the Input Range, your separate variable as the Factor, and your correlate as the covariate. Check the “Labels” and “Alpha” boxes, then enter the meaning of the level you want to use. The existing grades will  be subjected to a one-way ANOVA:

Step 6: Fill in the ANCOVA table

The variable that is being analyzed and tested for differences between groups.The number of observations or participants in each group. For each source of variation, you must compute the sum of squares, degrees of freedom, and mean squares, as well as the F-statistic and p-value for the regression. These computations can be done in Excel or another statistical software program.
Next, As you can see fill in the ANCOVA table properly.

Step 7: Analyze the results

Determine if the F ratio for X and C is significant (p < .05). If either is significant, you can conclude that the independent variable or covariate has a significant effect on the dependent variable. According to the ANCOVA table, the p-value for the research approach is 0.032. We can reject the null hypothesis that each of the studying methods leads to the same average exam score because this number is less than 0.05, even after accounting for the student’s existing grade in the class.