Creating XY Scattergraphs with Spreadsheets

Creating an xy scattergraph in Microsoft Excel
Creating an xy scattergraph in OpenOffice.org Calc 2.0

Creating an xy scattergraph in Microsoft Excel

  1. Select the data and click on the chart wizard button:
    Creating an xy scattergraph with a linear regression line and equations displayed.
  2. Choose an x-y scatter graph:
    scattergraph02 (16K)
  3. Choose finish:
    scattergraph03 (3K)
  4. Click on a point on the chart to highlight the data points:
    scattergraph04 (3K)
  5. Choose Chart: Add Trendline
    scattergraph05 (6K)
  6. Choose a linear regression and then click on the Options tab
    scattergraph06 (10K)
  7. Select both Display equation on chart and Display R-Squared value on chart (the image shows only the first item being selected.
    scattergraph07 (10K)
  8. The result:
    scattergraph08 (3K)

To obtain the slope and intercept in Excel for the above data, use the following functions:
=slope(c2:c8,b2:b8)
=intercept(c2:c8,b2:b8)

Note the use of the semi-colon. Excel uses a comma by default, OpenOffice 2.0 a semi-colon. OpenOffice.org 3.0 and 3.1 on Ubuntu 9.04 and 9.10 respectively use a comma. Distribution of OpenOffice.org 3.0 and 3.1 on Windows continue to use a semi-colon.

Excel 2007 uses different screens to obtain a linear regression.

7s

7t

7u

Creating an xy scattergraph in OpenOffice.org Calc 2.0

The following directions apply to OpenOffice.org Calc versions 2.0, 2.1, and 2.2. In version 2.3 the chart wizard was altered. Starting with version 2.3, the chart wizard does not wait for the user to use the mouse to drag the chart location. For directions on using version 2.3, refer to the notes on version 2.4.

  1. Select the data and click on the chart wizard
    scattergraph09oo (26K)
  2. Drag to indicate where the graph should be located and how big the graph should be
    scattergraph10oo (17K)
  3. Click on next
    scattergraph11oo (13K)
  4. Choose an xy chart
    scattergraph12oo (15K)
  5. The first variant works best in this course
    scattergraph13oo (15K)
  6. Choose create (the various titles can be filled in as appropriate)
    scattergraph14oo (13K)
  7. Choose insert statistics
    scattergraph15oo (20K)
  8. Choose the linear regression, click on OK
    OpenOffice Calc linear regression line
  9. The graph will display the best fit line, but not the function.
    scattergraph16oo (5K)

To display the slope and intercept in OpenOffice for the above data, use the following functions:
=slope(c2:c8;b2:b8)
=intercept(c2:c8;b2:b8)

Note the use of the semi-colon. Excel uses a comma by default, OpenOffice a semi-colon.