×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

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

## 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

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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!