Finding a linear model when the data does not follow a perfectly straight line may be referred to by several terms. The terms “method of least squares”, “linear regression”, or “best fit” are all used to refer to modeling data with lines.
We can also use WolframAlpha to model data with lines. To do this we type “linear fit” followed by the data listed as ordered pairs. Here is an example.
You get a graph, the model, and R squared values when you execute this command.
Like other spreadsheets, Google Sheets may be used to find a regression model for data. Following a few simple steps, we can graph a set of data in a scatter plot and find the corresponding model.
Suppose we have the demand data show in the table below.
To find a linear model for the Average Price per Gallon as a function of the Weekly Demand, we need to make a scatter plot of this data and add the linear regression model to it.
1. Start by opening a Google Sheet. In column A put the weekly demand data (the independent variables) and in column B put the average price per gallon (the dependent variable).
2. To graph the data, we first need to select it in the spreadsheet. Left mouse click in cell A1. While holding the mouse button down, drag the cursor to cell B6.
The data and labels at the top of the column should be highlighted.
3. From the Insert menu at the top of the window, select Chart.
4. A window will appear that allows you to create a variety of different types of graphs including scatter plots.
This window indicates where the data is located (A1:B6:) and indicates that the first rows contains headers or labels. There are several recommended chart, but none of them are scatter plots. To find a scatter plot, select Charts in the upper left corner of the window.
5. Select Scatter from the column of chart types on the left side of the window.
6. In the next column, choose the scatter plot at the top.
Now the preview on the right side of the window show a scatter plot.
7. To change the chart titles and axes labels, select Customize.
Under Chart Title, you should put a name that describes your chart. You can also change the color and size of the chart title font.
8. Use the scroll bar in the middle of the window to move farther through the chart options. Look for Axis Horizontal.
Give the horizontal axis an appropriate label under Title. You can also change the minimum and maximum extent of the graph by entering values into Min and Max.
9. Once you have given the horizontal axis a title, use the box next to Axis to choose Left Vertical.
Give the vertical axis an appropriate label under Title. Like the horizontal axis, you can change the vertical extent of the axis using Min and Max.
10. You may also add a trendline to the scatter plot. To do this, scroll farther down in the Customize window. Under the area labeled Series, look for Trendline. Selecting the box to the right give you several choices of regression for the data.
For example, select Linear to find the line of best fit. If you choose Use Equation for the Label and Show R2, the legend on the graph will show the equation as well as the coefficient of determination.
Select Insert to place the scatter plot in the sheet.
11. The equation in the legend may not include enough decimal places. If this is the case for a linear trend, the SLOPE and INTERCEPT commands may be used to calculate the slope and intercept of the trend. For this data, we would write
=SLOPE(B2:B6,A2:A6)
or
=INTERCEPT(B2:B6,A2:A6)
in a cell. Note that each of the functions require the range of the dependent variable to be listed first (B2:B6)and the range of the independent variable second (A2:A6).
12. If you need an output on the trend, the command FORECAST may be used. For instance, to find the y value on the linear trend at x = 130, we would type
In many linear application problems, you needed to write the given information as ordered pairs and then find the equation that passes through the ordered pairs. Here is a similar example that use p = mt + b instead of y = mx + b.
Problem The percent of births to teenage mothers that are out of wedlock can be approximated by a linear function. In 1960, the percentage was 15% and in 1996 the percentage was 76%.
Use this information to find a linear model for the percentage of births as a linear function of the number of years since 1950.
Solution Since the problem statement specifies a linear function of the number of years since 1950, the input to this function is years since 1950 and the output is the percentage. The information in the problem can be written as ordered pairs (10, 15) and (46, 76).
Define the variables for these quantities as
t: years since 1950
p: percentage of births to teenage mothers out of wedlock
This means the form of the linear function is p = mt + b. The slope of a line passing through these points is
The slope is written as a fraction so no rounding occurs. Writing this as a decimal and rounding the amount would lead to a line that does not pass through the points. With this slope, we know the equation of the line is . To find the value of b, substitute one of the ordered pairs into the equation and solve for b.
Equilibrium points are easy to find when the supply and demand functions are given by formulas….just set the formulas equal to each other to find the point of intersection. But what about when the supply and demand are data? The example below shows how to get the formulas for each function and then to find the equilibrium point.