When done, close the browser tab or window.
Start video.
McGraw-Hill Education red cube logo. Title screen: Use Functions to Build a Simple Linear Regression Equation. Because learning changes everything.™
Narrator: "In this video, we will use several functions in Excel to build a Simple Linear Regression Equation. This dataset shows the inventory of diamonds and the price of each diamond for a jeweler."
Column A, Shape; B, Size; C, Price; D, Cut Grade.
Narrator: "We believe there is a relationship between the size of the diamond and its price, and we'd like to build a regression equation that would allow us to predict the price based on the size. Let's put together the pieces of our regression analysis. First, calculate the correlation between our known X and known Y values. Known X would be the independent variable, the variable we believe will predict the dependent variable. In this case, we believe Size will predict Price. Let's use equals correl, open parenthesis, and choose our X values—not including the label—to the bottom of the column, comma, and we can start at the bottom of the Y values and go up and select not including the label. Close parenthesis, and we find a strong positive correlation between Price and Size."
The formula entered into cell J3: equals correl left parenthesis B2 colon B34 comma C2 colon C34 right parenthesis.
Narrator: "And we find a strong positive correlation between Price and Size. Next, we calculate the R-Squared value between Price and Size. Equals R-S-Q, and we see when we open parenthesis that we will select first the known Y values, and then the known X. Since Price is the dependent or variable to be predicted, that is our Y, we select that first. Comma, and then we select our known X values, close parenthesis. The R-Squared value of 0.97 says that the variation in Size predicts 97% of the variation in Price."
The formula entered into cell J5: equals r s q left parenthesis C2 colon C34 comma B4 colon B34 right parenthesis.
Narrator: "Next, we'll calculate the slope of the linear regression line between Price and Size. Equals slope, open parenthesis, again we're going to use known Y's first and then known X’s. We select the known Y values, comma, known X’s, end parenthesis."
The formula entered into cell L7: equals slope left parenthesis C2 colon C34 comma B16 colon B34 right parenthesis.
Narrator: "The slope is 9405.06. So as we scroll down, we see an additional carat of diamond would increase the price by approximately and equals point at the value for our slope."
Cell L14: equals L7.
Narrator: "Next, we'll calculate the intercept of the linear regression line between Price and Size. Equals intercept, open parenthesis, and we see it's just off the screen, but known Y's comes first, and then known X's, comma, close parenthesis."
Cell L9: equals intercept left parenthesis C2 colon C34 comma B2 colon B34 right parenthesis.
Narrator: "The intercept is -3681. Now in this case, we wouldn't expect to give a diamond away, that is a negative value. This literally is the point at which the regression line crosses the y-axis. So if we had a diamond of zero size, X would be zero, the line would cross the y-axis at -3681. The full regression equation is the coefficient that we calculated for Size—in other words, the slope—times whatever Size diamond we are trying to predict for, plus the intercept. So equals, and point at the intercept value."
Cell G12, equals L7; Cell J12, equals L9.
Narrator: "And that is our full regression equation, 9405.06 times X, or Size, plus -3681.44. Now we can forecast the price of a 2.4 carat diamond two different ways. The first way we can do it is by using the function forecast dot linear. Equals forecast dot linear, open parenthesis. We are to enter what is the X that we want to use as the Size in order to predict a value, and that size is 2.4. And then our known Y's. So we'll select all of our known Y's, comma, and our known X's. Close parenthesis, and the forecast is—just needs to have a little more space for that cell—18,890.71."
Cell I16: equals forecast dot linear left parenthesis 2.4 comma C2 colon C34 comma B2 colon B34 right parenthesis. The narrator increases the width of the column to view the result by clicking and dragging the right border of the column header.
Narrator: "The second way that we can calculate the forecast price for a 2.4 carat diamond is by using our regression equation. Equals the slope, times 2.4, which is the Size we're using to predict Price, plus, and then we click on the intercept."
Cell I17: equals G12 times 2.4 plus J12.
Narrator: "Both of these ways of calculating the predicted price will return the same result. In this video we've demonstrated how to build a simple linear regression equation using functions."
Video has ended.
Described transcript ©2024 McGraw Hill. All rights reserved. No reproduction or further distribution permitted without the prior written consent of McGraw Hill.
Close the browser tab or window.