Excel 2003 generates incorrect equations for regresssions -- denn.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If you graph data, then generate a trend line on the graph, then format the
tend line with add eqation, the equation generated is invariably and
obviously wrong. This is true for linear, polynomial and log fits (and maybe
the others).

For instance, use the following data for x and y. In this instance the y
data is generated by the equation y= 10+ 5x. Graph the data using the x
values for the x axis. Create a trend line (which works great and fits this
data exactly). Now, format the trend line by adding an equation to the plot.
The eqation that is generated is y=5x. The more complicated the data/fit
the worse the equation gets messed up.

-1 5
0 10
1 15
2 20
3 25
4 30
5 35
 
Use an "XY (Scatter)" chart instead of a "Line" chart.

The "Line" chart is misleadingly named and has nothing to do with whether
you want to draw a line or not. Rather a line chart assumes that x-data (if
given at all) are category labels instead of numbers. Why it offers to fit a
trendline at all with categorical data is mystery to me, but when you ask for
it, the trendline is based on fitting your y-data to x-data of 1,2,3,... for
which your posted equation is the correct but useless result.

Also note that the characterization of this group as "Excel Application
Errors" in Microsoft's Office Discussion Groups Home is also easily
misinterpreted. The real name of the group is
microsoft.public.excel.crashesgpfs and your problem has nothing to do with
Excel crashing. Hence the reason it took so long for someone to see your
post who has experience with your problem. You would have gotten an almost
immediate reply had you posted in the Charts or General Questions groups.

Jerry
 
Back
Top