×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

What is the trap in excel Chart Trendline(Regression)?
4

What is the trap in excel Chart Trendline(Regression)?

What is the trap in excel Chart Trendline(Regression)?

(OP)
My dear pals

I have the following x and y data

x             y
1.00         0,033
1.10         0,040
1,20         0,045
1,30         0,050
1,50         0,059
1,75         0,070
2,00         0,083

After drawing a chart carefully I notice that a good 3rd order polynomial really fits the curve with R squared of about 0.9989(what a fit!) and excel returns a polynomial y =0,0002x3 -0,0025x2 + 0,0134x + 0,0218.The intercept 0,0218 really satisfies my visual inspection as well as the P-curve (the trendline).

Problem : When I use the equation to regenerate y values using the same x-values only the first y becomes correct, the rest are very very less than reality.

Am I having an interpretation or processing or both problems?

Respects
IJR

RE: What is the trap in excel Chart Trendline(Regression)?

I got y = 0.0287x3 - 0.1329x2 + 0.2475x - 0.1101.  R^2 = 0.9998.  It works on all the range.

The data you showed had mixed mode decimal points (periods and commas).  I don't know if that's the problem.

Good luck,
Latexman

RE: What is the trap in excel Chart Trendline(Regression)?

(OP)
Latexman

Thanx for your fast response.

My decimal points and commas above should be taken as decimal points ie 0,10 is to mean 0.10. Did you fit my data with that reasoning?

Thanx anyway for checking

respects
ijr

RE: What is the trap in excel Chart Trendline(Regression)?

Yes, I took both as decimal points.

Good luck,
Latexman

RE: What is the trap in excel Chart Trendline(Regression)?

tip: make sure that you display enough significant figures in the trendline (format|number) to accurately reproduce the data.

RE: What is the trap in excel Chart Trendline(Regression)?

I am told that the equation for the trend line that is displayed can be innaccurate. I have not seen this myself.

Cheers

Greg Locock

RE: What is the trap in excel Chart Trendline(Regression)?

(OP)
Dear Pals

Thanx to Latexman for telling me something is wrong with me.

THE TRAP: CAREFULLY SELECT THE "TYPE OF CHART" TO USE BEFORE DOING INTELLIGENT WORK LIKE TRENDLINE REGRESSION

or at least that is what I have done to correct my trendline. Wrongly(for the usual routine of using charts for visual appeal only) I used curve type charts.

Correcting the chart type to "xy scatter with data connected by lines" got me the correct trendline.

Someone might want to elaborate on this

Respects
IJR

RE: What is the trap in excel Chart Trendline(Regression)?

I have found that on 4th order polynomials and above, the equation displayed is given to only one or two significant figures.  This means that if you use the data, you get a curve that does not fit the trend, due to rounding errors.  I've not found this problem with lower order polynomials.

In this case, I found that if you format the number of the equation to the maximum number of decimal places, rather than 'general', then this will often give more information.

RE: What is the trap in excel Chart Trendline(Regression)?

You can get strange numbers from the curve fit if you do a trendline when one or both axis are in a logarithmic scale.

//nisse

RE: What is the trap in excel Chart Trendline(Regression)?

IJR,

Please look at the following posting thread770-23296 regarding the linest function and its capabilities.

While i do use the trendline feature, I also use the linest function to determine the constants as well.

i hope this helps.
good luck!
-pmover

RE: What is the trap in excel Chart Trendline(Regression)?

Also beware that the typical Excel chart assumes the X values are in buckets - that is, they are discrete and not ordinal.  (So X1 is 1, X2 is 2, even if X2 is really 1.5)  I've had to specifically tell Excel that the data is time series, then change the number format on the chart.  But this usually only works with integer data.

Also don't forget that regressions are only good within the bounds of the orignial X's.

Good luck!

RE: What is the trap in excel Chart Trendline(Regression)?

(OP)
BML

That is why you have to be careful when you choose your chart type. XY scatter takes X and Y as they are and you can see them right on screen the way they are spaced apart

respects
ijr

RE: What is the trap in excel Chart Trendline(Regression)?

If you use the function LINEST you can return polynomial coefficients with the full 15 digit precision available in Excel.  For example consider that the X values are located in cells a1:a7 and the Y values are in cells B1:B7, then
select a10:d10 and enter the array formula

=LINEST(B1:B7,A1:A7^{1,2,3},TRUE,FALSE)

in a10 using the key combination Ctrl-Shift-Enter.

The order-3 best-fit polynomial coefficients are in A10:D10

If you want a fifth order polynomial

=LINEST(B1:B7,A1:A7^{1,2,3,4,5},TRUE,FALSE)
substituting A10:F10 for A10:d10

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close