Microsoft KB Archive/98452

{|
 * width="100%"|

XL4: Line Not Straight in Chart Created with Regression Tool

 * }

-

The information in this article applies to:


 * 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.

-or-

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:

  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   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).  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.

NOTE: To add a legend, click Add Legend on the Chart 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