Scatterplots

A scatterplot is a graph of plotted points obtained from the real-world situation of interest. The table below shows data that has been collected on gasoline consumption in the US between the years 1992 and 2004.

Year	’92	’93	’94	’95	’96	’97	’98	’99	’00	’01	’02	’03	’04
Consumption (billion of gallons)	110	111	113	116	118	119	123	125	126	128	131	133	136

 

You might recall seeing this example in the Linear and Geometric Growth Learn It pages. In the solution to that example, you were shown a graph plotted with data points in the form (year, consumption). Then you were asked to write the recursive and explicit forms to find a model for the data and use it to answer a question. It is important to understand how such a model can be built by hand. As the situations into which we seek insight become more complicated, though, technology can be of tremendous assistance. This section will help you apply your understanding of the characteristics of models by using a spreadsheet to handle the task of developing models fairly quickly and neatly. We’ll walk through the basics using the gasoline consumption example.

Spreadsheet Hands-On: Create a Scatterplot

The examples shown below will use the Microsoft Excel spreadsheet but you can also use an open source spreadsheet such as Apache OpenOffice Calc or Google Sheets.

Step 1: Storing the data.

Open a new blank worksheet and save it somewhere safe. You should save your work frequently as you go or turn on an auto-save feature if available. We’ll begin by typing the data from the gasoline consumption example into two columns, one labeled year and the other labeled consumption.

1. In cell B1 of your spreadsheet, type the word “Year.” In cell C2, type “Consumption.” Leave column A blank for now.
2. Beginning in cell B2, type the consecutive years from 1992 to 2004 each into a cell of its own, straight down. Spreadsheets have a feature to accomplish this quickly. Type just the first two cells, 1992 and 1993. Then highlight both. Grab the little “handle” in the lower right corner of the outline of the highlighted cells and drag it down to populate the remaining cells by continuing the series. In fact, in Excel, if you click the box that appears after populating a few cells, you get options for how you’d like for them to be filled: each a copy of the other, or a series as we just did.

 

3. Beginning in cell C2, type the consumption quantity that corresponds to each year.When you are done it should look like the image below. Click on any image on the page for a closer view in a new tab.

Step 2: Creating the scatterplot.

Click and drag to highlight cells B1 and C1 containing the words “year” and “consumption” then drag downward to highlight all the data points you entered.

1. Click “Insert” on the task ribbon then choose Scatter under the Charts block, and click the first scatter icon, Scatter.

 

2. A plot should appear showing each of your data points above their corresponding years.
3. You can change the formatting of the chart now or later. Let’s take care of the title while we have it open in front of us. Click on the word “consumption: and change the title of the chart by typing over the existing title. Let’s change it to “Gasoline Consumption in billions of gallons per year.” See the image below. You can obtain the subtitle text by shift-entering after the word “Consumption” then change it to a smaller font by selecting just the text you want to make small and changing its font size in the Home tab on the ribbon.

 

4. You can play around with the settings to adjust the color scheme and style of the chart elements by clicking on the chart and choosing the style box with a paintbrush icon that appears to the right of the chart or by clicking on the Design and Format tabs in the ribbon

You’ve just created a scatterplot of known data points in the gas consumption scenario. This scatterplot will make a nice presentation in a report or a display, but we’ll need to make an adjustment to the input data to be able to obtain an meaningful equation to use as a model. We’ll see how to choose an equation type to use to model the situation next.