Charting and trend lines

[jmw]

I have been tabulating data for caustic soda density vs concentration and temperature.
I then wanted to plot the data (which showed up two errors in the source data).
I needed to determine a good fit between the concentration and the density at 20degC and wanted to compare a linear fit and a second order polynomial (which then involved plotting the errors from the trend fits against the tabulated data).
Having selected my data I then went to the chart icon and selected line chart, plotted the curves and applied trend lines.
For the data 20% TO 30% I got a linear fit of y=2x or concentration = 2 x density, which is absurd.
I tried again using the scatter graph instead of line and this time got some reasonable results such that when I plotted the solutions I got a pretty good fit between the linear and the source data with minimal errors (y = 91.859x - 91.998).
So, why shouldn't I use "line" when graphing and why did it guive such poor results?

JMW

http://www.ViscoAnalyser.com

 

[bacon4life]

Check your x axis on the line graph. It is 1, 2, 3... instead of your X data?

 

[Clyde38]

Check this:

Scatter Chart or Line Chart?

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=190

 

[IRstuff]

It should be pretty obvious that a line chart assumes only constant spacings that are unrelated to your actual data. Once I figured that out, I've never used line charts again.

TTFN

FAQ731-376

 

[jmw]

Thanks all.
Thanks for the link Clyde38, it is helpful to get a good explanation.

IRStuff; a good approach for those of us that don't want to do a degree course in excel on the odd occasion we want a graph but as the link suggests, the layout and set up of graphing in excel is anything but intuitive or natural for that approach. I usually try "line" simply because its first and I would expect (in MS' "Intuitive" world) the more obscure and less useful stuff ought to come lower in the options.

Still, why "allow" a trend line in such a graph form?
Why does it give such strange results? If I didn't intend to check the results then I could have been in trouble, especially if I had something more obscure than y=2x; i.e. something not so patently absurd.

I wonder how many "Health scares" etc. come from publicity hound researchers with no better idea of how to use Excel and the like than me or who don't test their data to check that it makes sense.




JMW

http://www.ViscoAnalyser.com

 

[IRstuff]

The way out is to make the scatter plot the default. You select your columns to plot and hit F11 and the default chart type is selected for the data.

The erroneous trending on a line graph is probably driven by the simple fact the line graph has delta_x of 1

TTFN

FAQ731-376