Estimating values in 2, 3 order polynomial curves

[DianeD]

I have 2 similar questions regarding quadratic (2nd order) and cubic (third
order) polynomial curve fits.

Dataset 1 is comprised of the following (x,y) pairs: (5, 0.02305), (8,
0.03235), (12, 0.04655), (16, 0.07065), (20, 0.09195), (24,0.11935), (32,
0.17605) and (40,0.24485). After X-Y scatter, the resulting polynomial
equation results: y= 8E-05x^2 + 0.002X + 0.006. I have a variety of addtional
Y, for which I must solve for X using the quadratic formula- everything I try
works on the linear model, which is unacceptable.

Dataset 2 is comprised of the following (x,y) pairs: (0, 0.2795), (1,
0.2947), (2, 0.3113), (5, 0.3697), (10, 0.4756), (20, 0.6772), (60, 0.9729)
and (100, 1.6345). After X-Y scatter, the resulting polynomial equation
results: y= 3E-06x^3 - 0.000x^2 + 0.027X + 0.262. I have a variety of
addtional Y, for which I must solve for X using the cubic formula- everything
I try works on the linear model, which is unacceptable.

 

[Mike Middleton]

DianeD -

Maybe you need to use more significant digits. Here's the results I get when
I click the Increase Decimal button repeatedly on the trendline formula.

Dataset 1
y = 0.000083433350134*x^2 + 0.002640708447384*x + 0.006131225633048

Dataset 2
y = 0.000003011103155*x^3 - 0.000436225909118*x^2 + 0.027227135337526*x +
0.262411805746881

Or, you could use array-entered LINEST on the worksheet:

Dataset 1
=LINEST(known_y's,known_x's^{1,2})
8.343335013361470E-05 2.640708447383810E-03 6.131225633048880E-03

Dataset 2
=LINEST(known_y's,known_x's^{1,2,3})
3.011103155272290E-06 -4.362259091179450E-04 2.722713533752600E-02
2.624118057468820E-01

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[MartinW]

Hi Diane,

Since I have a memory like a sieve I keep a copy of this page
beside my computer and as my memory at least knows where
to find this page on the internet it's never far away no matter
where I am. I'm pretty sure you will find it as handy as I do.

http://j-walk.com/ss///excel/tips/tip101.htm

HTH
Martin

 

[MartinW]

Hi again Dianne,

I just plotted your dataset 1 and realised that the polynomial fit
is not an exact fit but a line of best fit.

If you want an exact fit from point to point you can achieve it like this.
With your x data in A1:A8
and your y data in B1:B8

Put a new x value in C1
and put this formula in D1
=PERCENTILE(B1:B8,PERCENTRANK(A1:A8,C1,300))

As you put new x values into C1, D1 will return the y values

It should be noted that this formula ONLY works when both x
and y values are ascending.

There are other approaches when your data is fluctuating,
some are worksheet functions only, some are VBA and some are UDF's

Here are more links you may find interesting.
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
http://www.tushar-mehta.com/publish_train/data_analysis/16.htm

HTH
Martin