XL4: Line Not Straight in Chart Created with Regression Tool

• Microsoft Excel for Windows, versions 4.0, 4.0a
• Microsoft Excel for the Macintosh, version 4.0

SYMPTOMS

If you use the Regression tool in the Analysis ToolPak, the chart created by selecting the Line Fit Plots option in the Regression dialog box may not contain a straight line.

CAUSE

The line is not straight because the chart that is created is a line chart instead of an xy (scatter) chart.

STATUS

This is a known problem in Microsoft Excel 4.0. This problem does not occur in Microsoft Excel versions 5.0 and later.

MORE INFORMATION

Because a line chart is created instead of an xy chart, the resulting chart does not contain a straight line for the predicted Y values. This problem occurs when the X values are not sequential. For example, if the X values are 1, 2, 3, 4, 5, 6, and so on, a line chart will plot a straight line for the predicted Y values. However, if the X values are 1, 2, 5, 6, 10, and 12, a line chart will not plot a straight line for the predicted Y values.

In order to plot a straight line for the predicted Y values, you must use an xy (scatter) chart.

WORKAROUND

Change the chart that was created with the Regression tool to an xy (scatter) chart:

1. On the Gallery menu, click XY (Scatter).

2. On the Chart menu, click the Edit Series command to change the X-values range of the two series to the actual X-value range (input x-range). (The first series is the predicted Y values and the second series is the original Y values.)

   Note: You could also change the text on the x-axis from Observation to X.

Use this next workaround if you want the chart to be updated when the data changes, and you do not want to have to run the Regression tool to obtain updated results.

Use the following example as a guide to create your own xy (scatter) chart:

1. Create a spreadsheet with the following data:

         A1: X    B1: Y      C1: Predicted Y
         A2: 1    B2: 40
         A3: 2    B3: 80
         A4: 5    B4: 100
         A5: 6    B5: 120
         A6: 10   B6: 110
         A6: 12   B7: 130
   
   

2. Select cells C2 through C7, and type the following array formula:

         =TREND(B2:B7,A2:A7)
   

   Note: To enter this formula as an array, press CTRL+SHIFT+ENTER (if you are using Microsoft Excel for Windows), or press COMMAND+ENTER (if you are using Microsoft Excel for the Macintosh).

3. Select cells A1:C7, and click New on the File menu. Select the Chart option. (The first column contains X values for an xy [scatter] chart.) To add lines that connect the points, click the second xy (scatter) chart type on the Gallery menu.

For more information on curve fitting in Microsoft Excel, query on the following words in the Microsoft Knowledge Base:

   excel and curve and fitting

REFERENCES

"Microsoft Excel Function Reference," version 4.0, page 436 "Microsoft Excel User's Guide 1," version 4.0, page 413 "Microsoft Excel User's Guide 2," version 4.0, pages 41-45