Forming a trend line
 

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.

Forming a trend line
 

On Jun 29, 12:22 am, Aaron wrote:
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.

I have also tried plotting it with word as an xy graph in word, and
the best trendline is the 3 polynominal exponential line.

How do I use this forula in excel to make a missing data formula.

Cheers,

Aaron.

Forming a trend line
 

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.

Forming a trend line
 

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.

Forming a trend line
 

On Jun 29, 2:04 am, Jerry W. Lewis wrote:
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
roups.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.- Hide quoted text -

- Show quoted text -

When I try the formulas and methods suggested in the above replies, I
get results that are not good enough. If I enter the knowns even, IE
850 it gives me a different number than I supplied the formula to
start with.

The 3rd order poly plotting it on a graph fits this data series
perfectly, but when I use the linest it gives me values that are not
fitting a 3rd order polynominal.

Any more guidance or what am I not understanding here? I am not a
stats man at all.

Cheers,

Aaron.

Forming a trend line
 

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.

Forming a trend line
 

Unless you know both the functional form of the relationship and that the
existing data exactly fits that functional form, your expectations are
unrealistic.

Fitting a particular kind of trendline determines that trendline of that
form that minimizes the disrepancies for the existing points, although it may
not exactly pass through any of the existing points.

A 7th degree polynomial would exactly fit the known data, but would probably
be completely useless for interpolation unless you had external knowledge
that a 7th degree polynomial is the correct model.

An alternative would be to draw a piecewise or smooth curve through the
existing data, but without a functional form for that curve, interpolation
may be less accurate than with a well chosen trendline. Approaches to
interpolation would include
linear: 181.1081
cubic spline: 158.7512
bezier curve: 180.3290
ratio of linears using a=550,555,740: 166.5652
ratio of linears using a=555,740,850: 178.1682

Again, in the absence of actual knowledge of the functional form, possible
interpolated values span a large range

The chart smoothed curve appears to use bezier curves.
http://www.xlrotor.com/Smooth_curve_...ample_file.zip
Cubic splines are very similar, but will have more bounce for rapidly
changing curvature. David Braden has posted code for cubic splines
http://groups.google.com/group/micro...2966520eccdb1f

"Aaron" wrote:

On Jun 29, 2:04 am, Jerry W. Lewis wrote:
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
roups.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.- Hide quoted text -

- Show quoted text -

When I try the formulas and methods suggested in the above replies, I
get results that are not good enough. If I enter the knowns even, IE
850 it gives me a different number than I supplied the formula to
start with.

The 3rd order poly plotting it on a graph fits this data series
perfectly, but when I use the linest it gives me values that are not
fitting a 3rd order polynominal.

Any more guidance or what am I not understanding here? I am not a
stats man at all.

Cheers,

Aaron.