Thanks for showing =TREND(Bdata,Adata^{1,2},640^{1,2}). I had (stupidly)
tried =TREND(Bdata,Adata^{1,2},640) - that is I forgot the powers on the
new-x values.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Jerry W. Lewis" wrote in message
...
You can avoid the coefficients altogether by using the TREND function,
e.g.
=TREND(Bdata,Adata^{1,2,3},640^{1,2,3})
gives 181.8758 directly.
In the absence of a theoretical basis for a cubic model, I would tend to
distrust the the extra wiggle that it introduces. A quadratic
=TREND(Bdata,Adata^{1,2},640^{1,2})
gives 161.9057, and a log-linear mode
=EXP(TREND(LN(Bdata),Adata,640))
gives 181.2075, suggesting that the OP's "very accurately" criterion is
only
partially achievable without a theoretical model.
A log-log model
=EXP(TREND(LN(Bdata),LN(Adata),LN(640)))
gives the intermediate result of 174.3289.
The OP's comment that
the second set of numbers decrease as the first set increase.
is irrelevant to whether simple linear regression is appropriate; it
merely
implies that the slope of the fitted line would be negative. The real
issue
(as Bernard correctly noted) is that a plot of the data suggests some kind
of
curvature.
Jerry
"Bernard Liengme" wrote:
In Excel make an XY chart with the A-values (excluding the 640) as the
x-values and B values as the y-values.
Experiment with polynomial trendline to find a good fit. But do not use
the
values from the trendline to extrapolate since you will need to be so
careful typing them. Rather use LINEST to get these values into cell -
see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
Now let's suppose you have a third order fit with the LINEST values in
C1:F1
Put 640 (new x-value) in C2 and in D2 enter =C1*C2^3+D1*C2^2+E1*C2+F1
to compute new y-value ( I get 181.8758 but since the other values are
only
know to zero decimal places I am not sure how much faith I would but in
the
POINT 8758)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Aaron" wrote in message
oups.com...
Hi,
I have two sets of numbers and I need to be able to interpolate very
accurately the numbers I dont know for the A column not al lof them
just say 680 for example. What would the corresponding B colum number
be?.
A
270
370
409
440
550
555
640 (new number in series)
740
850
B
411
288
264
236
210
205
XXX (Unknown)
153
131
I have tried linear regression and it doesent work, probably because
the second set of numbers decrease as the first set increase.
Any advice greatly appreciated.
Cheers,
Aaron.