Approximate Linear Models

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 R², 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)

=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

=FORECAST(130,B2:B6,A2:A6)

A number of you have asked me about alternatives to a TI graphing calculator. In addition to Excel, there are many online graphing tools available. If you have an Android phone or IPhone, there are a huge number of free apps that are available. I have had a hard time finding just one that does everything we need. Most will graph formulas, but may or may not graph data and fit data to lines. None of them work identically to a graphing calculator which makes them difficult to support. But luckily there is another option!

Another option is the website WolframAlpha (http://www.wolframalpha.com/). This website is the Internet’s leading computational engine. It can do just about anything. The trouble is knowing how to use it to do just about anything.

When you go to this website, you’ll see a box in which you can enter commands to help you do mathematics. The list of things you can do is HUGE. It is best to show some examples to get you started.

For instance, suppose you want to graph the function y = -5.686x + 676.173 in a window from x = 0 to x = 25. Enter the text you see below into WolframAlpha followed by Enter.

WolframAlpha makes a nice graph. If you want to graph two formulas simultaneously, add another formula with the word “and” as you see below.

You can see that these lines are going to meet. To see this point of intersection, extend the graph by modifying the input to WolframAlpha as you see below.

We can find this point of intersection by modifying the input to WolframAlpha with the command “intersections”:

Not only does it give a decimal…it also gives the exact answer in terms of fractions!

Often you are interested in evaluating the model above at a particular point. We can do this by clicking on the formula above. This will make WolframAlpha graph the formula by itself. Now add “where x = 20” on the command line and you will see:

This gives the same output as TRACE on a graphing calculator. I’ll continue to make posts about WolframAlpha so those of you who do not have access to a graphing calculator have an alternative way to make graphs, calculate values, ect.

FAQ Topic: Approximate Linear Models

How Do I Do Linear Regression in Google Sheets?

How Do I Use WolframAlpha To Graph a Formula?