StatisticsExcel_SimpleRegressionAnalysis

Simple Regression

• When to use it: To predict the value of a dependent variable Y from on independent variable X

Example

Revisiting hardness ($y$) vs. quenching temperature ( $x$) from the Regression Module

Temp ($x$) Hardness ($y$) $x^2$ $xy$ $y^2$
101 49 10201 4949 2401
115 44 13225 5060 1936
115 46 13225 5290 2116
140 38 19600 5320 1444
123 43 15129 5289 1849
107 47 11449 5029 2209
135 41 18225 5535 1681
135 38 18225 5130 1444
105 47 11025 4935 2209
110 45 12100 4950 2025
110 43 12100 4730 1849
135 37 18225 4995 1369
125 44 15625 5500 1936
132 40 17424 5280 1600
130 39 16900 5070 1521
SUM 1818 641 222678 77062 27589

Using Excel

Under Data, Data analysis, select REGRESSION and enter the ranges for X (Temp) and Y (Hardness) and hit OK.

You can also obtain the following graph by selecting both X and Y data range first, then hitting Insert-Scatter-Chart layout 3. Adjust both min and max axis values by selecting each axis scale and hit the right mouse button to pick the “Format axis” option.

The corresponding model is:

Hardness = 75.26 - 0.2684 Temp

Although it’s vital to check model assumptions, these are not going to be covered here.

Test of the Significance of the Regression Model

$H_0: \beta_1 = 0$ There doesn’t exist a linear relationship between $x$ and $y$. The regression model has no meaning.

$H_a: \beta_1 \neq 0$ $x$ is valuable to explain the variation in $y$.

ANOVA Table

Sources of Variation SS df MS F
Regression SSR 1 MSR = $\frac{\text{SSR}}{1}$ $\frac{\text{MSR}}{\text{MSE}}$
Error SSE n - 2 MSE = $\frac{\text{SSE}}{n - 2}$
TOTAL SST n - 1

The hypothesis: $H_0: \beta_1 = 0 \quad \quad H_a: \beta_1 \neq 0$
The test statistic: $F = \frac{MSR}{MSE}$
The conclusion: Reject $H_0$ if $F \gt F(tables) = F_{\alpha, \, 1, \, n - 2}$

As part of the previous analysis using Excel you get the following ANOVA table:

Since the SIGNIFICANCE F (or p-value) is less than an alpha value of 0.05 then the regression model is statistically significant.

Example

Develop a regression model for Voltage vs. Current and interpret its coefficients.

Voltage Current Voltage Current
5.3 0.12 101.9 1.05
23.2 0.21 115.3 1.18
25.8 0.29 117.3 1.24
44.9 0.44 133.6 1.32
58.7 0.55 135.5 1.44
64.2 0.59 145.9 1.48
72.3 0.41 167.2 1.60
76.1 0.84 171.9 1.75
96.5 0.93 179.4 1.83