[Statistics] Interpreting Regression Analysis Output in Excel

When you perform a regression analysis in Excel,
a table like the one below is output.

In this article, I will
explain how to interpret these output resultsin
an easy-to-understand way.

There are three points to check in the regression analysis output.
・Contents of the regression equation
・Goodness of fit
・Significance of regression coefficients (P-value)

Let's look at them in order.

Checking the contents of the regression equation

As you know, the regression equation is the following formula.

Here, I am using a simple regression equation with one variable as an example.

y is called the objective variable, and X is called the explanatory variable. Both are variables.

What is calculated in regression analysis are the values of a and b, where
a is called the regression coefficient and b is called the intercept.

In the regression analysis output, these are displayed at the very bottom.

According to this output result,
the regression equation is as follows.

Checking the goodness of fit

Goodness of fit refers tothe accuracy of the regression equation.

It means, how welldoes the regression equation correctly explain the relationship between X and Y?

You can find this out using R2, thecoefficient of determination.

The coefficient of determination"basically" takes a value between 0 and 1, and the closer it is to 1, the better the fit.
(This "basically" is important. I will explain it later.)

In the diagram on the left,
the difference between each data point and the regression equation (this is called theresidual) is small, and it can be said that the fit is very good. In this case, the coefficient of determination is close to 1.
On the other hand, in the diagram on the right,
the residual is large, and it cannot be said that the fit is good. The coefficient of determination is close to 0.

The coefficient of determination is output as R2 in the regression analysis output.

R2 means R squared, and R stands for the correlation coefficient.
In other words, the coefficient of determination is the square of the correlation coefficient.

In this output, the coefficient of determination is 0.2, so the fit does not seem very good.

Note that the formula for the coefficient of determination (R²) in regression analysis is as follows:
R² = 1 - Sum of Squared Residuals ÷ Total Sum of Squares
Applying this to the output above:
R² = 1 - 0.1123 ÷ 0.1417 = 0.2074.

The following is an image.
The coefficient of determination (R²) can also be rephrased as the regression sum of squares ÷ total sum of squares.

Checking the significance of regression coefficients

The coefficient of determination (R²) indicates the goodness of fit of the model, but it does not directly indicate whether the relationship between the objective variable and the explanatory variable is statistically significant.
It is necessary to check whether there is a significant relationship between the objective variable and the explanatory variable through a significance test of the regression coefficients.

In fact, when performing multiple regression analysis with multiple explanatory variables, some explanatory variables may appear that do not affect the objective variable.
To improve the accuracy of the regression equation, such explanatory variables with low significance must be dropped from the equation.

What is the significance of regression coefficients?

In regression analysis, this indicates whether each explanatory variable has a statistically meaningful effect on the objective variable.

Having a meaningful effect means that the regression coefficient is not 0.
If we can say that the regression coefficient is not 0, we can say that the explanatory variable affects the objective variable, right?

How to check for significance

The significance of the regression coefficient can be determined from the P-value in the output.
If the P-value is extremely small, the regression coefficient is judged to be statistically significant.

The question remains: what level should we specifically define as "extremely small" here? This level is called the significance level, and it is generally set to 5%.

For example, when the significance level is set to 5%,
if the P-value is below 0.05,it is said to be statistically significant.

In the above case, the P-value is 0.013, so the regression coefficient is statistically significant, and it can be said that the explanatory variable X significantly affects the objective variable.

Meaning of the P-value

Finally, what is the P-value in the first place?
I would like to look at its meaning in a little more detail.

The P-value in regression analysis is a statistic that indicates the probability of obtaining the observed data (or data even more extreme) under the null hypothesis that the regression coefficient is actually 0.

The image is as follows. (The gray area is the P-value)

Let's explain this using an example of investigating the effectiveness of a new drug.

Situation:
A company has developed a new headache medication. They administered this drug to 100 patients, and symptoms improved in 70 of them.

Meaning of the P-value:
The P-value indicates the probability that symptoms would improve in 70 or more out of 100 people, assuming that 'this drug has no effect (is the same as a placebo effect).'

Interpretation:
Small P-value: It is difficult to explain the improvement in 70 or more people by chance. It is highly likely that the drug is effective. Large P-value: Improvement in about 70 people could occur even with a placebo. There is insufficient evidence to claim the drug's effectiveness.

In other words, the P-value represents the 'probability that this result (or a more extreme result) would occur by chance, assuming the drug has no effect.'
The smaller the P-value, the stronger the evidence for considering the drug effective.

Summary

We have explained the key points for interpreting the output results of regression analysis in Excel.
By understanding the three elements—the content of the regression equation, the goodness of fit, and the significance of the regression coefficients—you can significantly improve the quality of your data analysis. Furthermore, by properly understanding and utilizing these concepts, you will be able to gain deeper insights and enhance the quality of your data-driven decision-making.

Statistical analysis can sometimes feel complex, but by deepening your understanding step by step, it becomes a powerful analytical tool.
I hope this article helps you improve your data analysis skills.