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

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

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

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

Good luck,

Latexman

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

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

Cheers

Greg Locock

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

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

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

//nisse

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

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

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

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