Thursday, May 29, 2014

Overview of Simple Linear Regression in Excel 2010 and Excel 2013

This is one of the following seven articles on Simple Linear Regression in Excel

Overview of Simple Linear Regression in Excel 2010 and Excel 2013

Complete Simple Linear Regression Example in 7 Steps in Excel 2010 and Excel 2013

Residual Evaluation For Simple Regression in 8 Steps in Excel 2010 and Excel 2013

Residual Normality Tests in Excel – Kolmogorov-Smirnov Test, Anderson-Darling Test, and Shapiro-Wilk Test For Simple Linear Regression

Evaluation of Simple Regression Output For Excel 2010 and Excel 2013

All Calculations Performed By the Simple Regression Data Analysis Tool in Excel 2010 and Excel 2013

Prediction Interval of Simple Regression in Excel 2010 and Excel 2013

 

Overview of Simple Linear

Regression in Excel

Linear regression is a statistical technique used to model the relationship between one or more independent, explanatory variables and a single dependent variable. The linear regression type is classified as Simple Linear Regression if there is only a single explanatory variable. The regression type is classified as Multiple Linear Regression if there is more than one explanatory variable.

 

The Regression Equation

The end result of linear regression is a linear equation that models actual data as closely as possible. This equation is called the Regression Equation. The more linear the relationship is between each of the explanatory variables and the single dependent variable, the more closely the Regression Equation will model the actual data.

In the Regression Equation, the variable Y is usually designated as the single dependent variable. The independent explanatory variables are usually labeled X1, X2, …, Xk.

The Regression Equation for multiple regression appears as follows:

Y = b0 + b1X1 + b2X2 + … + bkXk

The Regression Equation for simple regression appears as follows:

Y = b0 + b1X

b0 is the Y-intercept of the Regression Equation.

b1, b2, ..,, bk are the coefficients of the independent variables.

The most important part of regression analysis is the calculation of b0, b1, b2, ..,, bk in order to be able to construct the Regression Equation

Y = b0 + b1X for simple regression

or

Y = b0 + b1X1 + b2X2 + … + bkXk for multiple regression.

 

Purposes of Linear Regression

Linear regression, both simple and multiple linear regression, generally have two main uses. They are as follows:

1) To quantify the linear relationship between the dependent variable and the independent variable(s) by calculating a regression equation.

2) To quantify how much of the movement or variation of the dependent variable is explained by the independent variable(s).

 

The Inputs For Linear Regression

The input data for linear regression analysis consists of a number of data records each having a single Y (dependent variable) value and one or more X (explanatory independent variable) values. Simple regression has only a single X value. Multiple regression has more than one X (independent) variable for each Y (dependent) variable.

Each data record occupies its own unique row in the regression input. Each data record contains the specific values of the input (independent) X variables that are associated with a specific value of the dependent Y variable shown in that data record.

The input data for multiple regression analysis appear as separate data records on each row as follows:

Y X1 X2 … Xk

4 6 10 …15

5 7 11 …16

6 8 12 …17

7 9 13 …18

8 10 14 …19

 

Simple Linear Regression

Simple regression has only a single X (independent) variable. Simple linear regression is sometimes called bivariate linear regression. Simple linear regression uses single independent variable (X) known as the explanatory, predictor, or regressor variable. The single dependent variable (Y) is the target or outcome variable.

Simple linear regression requires that both the dependent variable and the independent variable be continuous. If ordinal data such as a Likert scale is used as a dependent or independent variable, it must be treated as a continuous variable that has equal distance between values. Ordinal data is normally defined data whose order matters but not the differences between values.

The input data for simple linear regression analysis appear as separate data records on each row as follows:

Y X

4 6

5 7

6 8

7 9

8 10

 

Null and Alternative Hypotheses

The Null Hypothesis of linear regression states that the coefficient(s) of the independent variable(s) in the regression equation equal(s) zero. The Alternative Hypothesis for linear regression therefore states that these coefficients do not equal zero.

For multiple linear regression this Null Hypothesis is expressed as follows:

H0: b1 = b2 = … = bk = 0

For simple linear regression this Null Hypothesis is expressed as follows:

H0: b1 = 0

b1 is the slope of the regression line for simple regression.

The Alternative Hypothesis, H1, for linear regression states that these coefficients do not equal zero.

The Y Intercept b0 is not included in the Null Hypothesis.

 

X and Y Variables Must Have

a Linear Relationship

Linear regression is a technique that provides accurate information only if a linear relationship exists between the dependent variable and each of the independent variables. Independent variables that do not have a linear relationship with the dependent variable should not be included as inputs. An X-Y scatterplot diagram of between each independent variable and the dependent variable provides a good indication of whether the relationship is linear.

When data are nonlinear, there often two solutions available to allow regression analysis to be performed. They are the following:

1) Transform the nonlinear data to linear using a logarithmic transformation. This will not be discussed in this section.

2) Perform nonlinear regression on the data. One way to do that is to apply curve-fitting software that will calculate the mathematical equation that most closely models the data. Another section in this book will focus on using the Excel Solver to fit a curve to nonlinear data. The least-squares method is the simplest way to do this and will be employed in this section.

 

Do Not Extrapolate Regression

Beyond Existing Data

The major purpose of linear regression is to create a Regression Equation that accurately predicts a Y value based on a new set of independent, explanatory X values. The new set of X values should not contain any X values that are outside of the range of the X values used to create the original regression equation. The following simple example illustrates why a Regression Equation should not be extrapolated beyond the original X values.

 

Example of Why Regression Should Not Be Extrapolated

Imagine that the height of a boy was measured every month from when the boy was one year old until the boy was eighteen years old. The independent, explanatory X variable would the month number (12 months to 216 months) and the dependent y variable would be the height measured in inches. Typically most boys stopped growing in height when they reach their upper teens.

If the Regression Equation was created from the above data and then extrapolated to predict the boy’s height when he reached 50 years of age, the Regression Equation might predict that the boy would be fifteen feet tall.

 

Linear Regression Should Not

Be Done By Hand

Excel provides an excellent data analysis regression tool that can perform simple or multiple regression with equal ease. Doing the calculations by hand would be very tedious and provide lots of opportunities to make a mistake. Excel produces a very detailed output and the regression tool is run. I have recreated all of the simple regression calculations that Excel performs in this chapter. It will probably be clear from viewing this that it is wise to let Excel do the regression calculations. A number of statistics textbooks probably place too much emphasis on teaching the ability to perform the regression equations by hand. In the real world regression analysis would never be done manually.

The best way to understand simple linear regression is to perform an example in the following blog article.

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

 

Posted by at

3 comments:

  1. thea sandersNovember 13, 2023 at 12:38 PM

    Where's the go-to spot for Dianabol (USA) Spectrum Pharma and Testestoron? Need a reliable source and some wisdom on dosing. Any input is welcome!

    ReplyDelete
  2. JohnWalterNovember 13, 2023 at 12:39 PM

    Hey there! When it comes to scoring Testestorone, misterolympia.shop is your golden ticket. I've had success with their products—genuine and powerful. The ordering process is smooth as silk. Now, for dosage advice on Dianabol, my tip is to kick off with 20mg a day for beginners. Gauge your body's response, and adjust accordingly. Why misterolympia.shop? Their reliability and product excellence set them apart. You should definitely check out this page testosterone for sale
    Trust me, it's the go-to for your fitness journey.

    ReplyDelete
  3. SamNovember 13, 2023 at 12:40 PM

    Had to shout out about misterolympia.shop. Oxandrolone 10MG/TAB Magnus is a winner—legit, rapid delivery, and results that rock. Misterolympia.shop isn't just a site; it's your reliable comrade in the fitness grind.

    ReplyDelete

Subscribe to: Post Comments (Atom)