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_...nalysis/16.htm
HTH
Martin
"MartinW" wrote in message
...
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
"DianeD" wrote in message
...
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.