StatisticsExcel_SimpleRegressionAnalysis

Simple Regression

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

For more information about Simple Regression click here.

 

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

 

 

Running a Simple Regression Analysis

 

Do it yourself

Step 1: Go to "Attachments" on the bottom of this page and dowload the Excel spreadsheet.

Step 2: Follow the steps teached in the video above and solve the exercises in the Excel spredsheet dowloaded. 

Step 3: Answer the questions in the Interactive Content below.