Microsoft Excel provides graphing capabilites on many levels. We will look at several ways to use these tools.

Illustration 1 :

Graph a line given two points on the line ---> (4, 2) and (6, -8)

Steps:

1. Enter x-coordinates in one row and y-coordinates in another row.

2. Click on the graphing icon.

3. Highlight the data range (cells containing the x and y-coordinates).

4. Choose Series and highlight the two x-coordinates for the X-Values and the two y-coordinates for the Y-Values.

5. Add titles for the chart, x-axis, and y-axis.

6. Place the chart as an object in Sheet 1.

Click here to see what the graph should look like.


Illustration 2 :

Graph a scatter plot based on a set of data.

 Outside Temperature (F)

Cricket Chirps (per minute)

 51

16

67

33

82

66

102

98

1. Enter the data into an Excel worksheet.

2. Click on the graphing icon.

3. Choose the XY (scatter) chart type.

4. Choose only cells containing values for the data range.

5. Add appropriate titles for the chart, x-axis, and y-axis.

6. Place the chart as object in Sheet 1.

Click here to see what the graph will look like.


Illustration 3 :

Based on the scatter plot in Illustration 2, we can write an equation for the graph. Even though the graph is not a perfect line, it does "approximate" a line. We can draw a line (called a "line of best fit") following the path of the data making sure to cross through at least two data points. Based on these two points, we can find a slope and writewhat we call a "prediction equation".

Using the data above, let's write an equation.

The "line of best fit" goes through the data points (102, 98) and (67, 33). Based on this information, we know the slope is . Using this slope and one of the points we arrive at an equation y = .94x - 30.115.

We can now use this equation to make "predictions". For example:

 If the Outside Temperature (F) is:

then the Cricket Chirps (per minute) would be

 45

12

70

36

110

73


Student Activities

 

Assignment 1:

1. Create an Excel worksheet containing a table with to coordinates (6, 2) and (-2, -9).

2. Graph these points using Excel. Place the graph below the worksheet. Be sure to include appropriate titles.

3. Place your name on the worksheet and print a copy.

 

Assignment 2:

1. Using the data below, set up an Excel worksheet with this data.

 Miles Driven

Gallons of Gas Used

22

1

43

2.5

90

4.7

120

5.9

2. Create an XY scatter plot based on this data. Include appropriate titles.

3. Place your name on the graph and print a copy. Draw a line of best fit and find the following information:

slope (m)

relationship between the data

prediction equation

If the  Miles Driven are

then the Gallons of Gas Used would be

15

36

75

200


Return to Instructional Unit