Create the same Polynomial equation made by chart

Kobee

New Member
Joined
Mar 6, 2003
Messages
47
I am in over my head and need some help.

We have about 3000 numbers which are all very small - the only way to evaluate the quality of our data is to chart it in a line chart and then add a polynomina trendline to the order of 3. We then take the equation which is displayed and apply it to each of the values in our dataset. The range from all the results is then produced.

What I would like to do come up with the same formula that excel made for the trend line without making the chart. This data is produced frequently and of the 5 sets I have each one had a different equation.

Below is a small amount of detail - I would appreciate any help - I have looked though many posts on this subject but still lost.

Equation created and displayed in chart
y = 4E-13x3 - 2E-09x2 + 2E-06x - 0.0004


Sampling of data and sampling of corresponding result of above equation where x = data
0.0002 9.999990E-05
0.0003 9.999987E-05
0.0004 9.999984E-05
0.0005 9.999981E-05
0.0005 9.999979E-05
0.0006 9.999976E-05
0.0006 9.999976E-05
0.0003 9.999986E-05
0.0002 9.999990E-05
0.0002 9.999994E-05
0.0001 9.999996E-05
0.0001 9.999997E-05
0.0001 9.999996E-05
0.0002 9.999993E-05


Final Range of values produced

1.0434684E-09


Thanks
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi,
What you're trying to do is called 'polimonial regression' (specifially third-order looking at what you've specified).

Look this up.
 
Upvote 0
PaddyD

I do not understand the Linest with my data - I only know the x in the formula and Excel has created the rest of the regression formula. Can I create the same regression formula automatically like excel does when checked in the chart? Depending on the specific data Excel creates the formula differently. Is it possible to generate the other components of the formula? Is this what the linest does?

y = -9E-14x3 + 4E-10x2 - 5E-07x + 0.0001
or
y = 4E-13x3 - 2E-09x2 + 2E-06x - 0.0004
or
y = 3E-13x3 - 1E-09x2 + 2E-06x - 0.0004

I would be happy to send you the sheet I have with the data and chartss on it if that would show more of what I am trying to end up with.

Thanks
 
Upvote 0
OK - I took your data, & stuck it on a sheet (a1:b14). Did an xy scatter, & added a polynomial trendline (order = 3).

The equation produced was:

y = -0.5539x3 + 0.0007x2 + 7E-07x + 0.0001

now, entering the formula:

=LINEST(B1:B14,A1:A14^{1,2,3})

as an array formula into three cells at once (using control + shift + enter, not just enter), I get the following:
Book1
ABCDEF
10.00021.00E-04-0.5538890.00073-6.80651E-07
20.00031.00E-04
30.00041.00E-04
40.00051.00E-04
50.00051.00E-04
60.00061.00E-04
70.00061.00E-04
80.00031.00E-04
90.00021.00E-04
100.00021.00E-04
110.00011.00E-04
120.00011.00E-04
130.00011.00E-04
140.00021.00E-04
Sheet1


...notice the similarity!
 
Upvote 0
PaddyD,

I am affraid I have confused the issue in my original post. The dataset is only column A. Column B was a calculated value not used in producing the chart or the equation. I only have one set of data and thus some of my own confusion on understanding Linest.

Tried to reproduce your example to help my understanding but had trouble getting similar numbers or matching the equation.

What I have done exactly is created a Line Chart with Series A1:A2995, added a trendline order =3 with equation.
Trendline2.xls
ABCDEFGH
10.0002
20.0003Source data = ("A1:A2995")
30.0004
40.0005
50.0005
60.0006Chart created equation
70.0006y = -8E-14x3 + 3E-10x2 - 4E-07x + 1E-04
80.0006
90.0006
100.0006
110.0006
120.0006
130.0006
140.0006
150.0005
160.0004
170.0004
180.0003
190.0002
200.0002
210.0001
Sheet1


You have certainly offered some help here and I appreciate it very much - if this changes the use of the Linest your continued help would be great.
 
Upvote 0
try:

=LINEST(A1:A2995,ROW(INDIRECT("1:2995"))^{1,2,3})

...same approach as before - array entered into 3 cells using control + shift + enter. With only one data series (y values), you need to construct the x values on the fly, hence the ROW(INDIRECT("1:2995") bit, which constructs an array {1,2,3...2995} to feed into the linest()
 
Upvote 0
PaddyD,

That works great - The numbers are very close to those generated by the chart.

Thank You Very Much!

Chris
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top