how to get values in between data?

[Oligo]

hi,
if i have 2 column of data like
A B
100 10
200 36
300 45
400 60

how can i get the b values for input of values that are in between my column
A data? im currently ploting out the graph and using the trendline eqn to
obtain my results.however polynomial power6 curve still cannot approach my
table data well.
wonder if i can do some what-if-analysis to interpolate the 2 column?

 

[Ron Rosenfeld]

hi,
if i have 2 column of data like
A B
100 10
200 36
300 45
400 60

how can i get the b values for input of values that are in between my column
A data? im currently ploting out the graph and using the trendline eqn to
obtain my results.however polynomial power6 curve still cannot approach my
table data well.
wonder if i can do some what-if-analysis to interpolate the 2 column?

Given the data you present above, a 3rd order polynomial as an R^2 of 1. Can't
get too much better fit than that.

If your "real" data doesn't match the data you've presented, you'll have to
make some decision as to how you will interpolate between points.

One common problem in trying to apply the formula on a graph to real data is
the precision of the values displayed. If that is what you are trying to do,
and is your problem, right-click on the formula, then select "format trendline
label". Format the number to 15 decimals and use those values.

Or, you could use the LINEST equation. To use the LINEST equation to predict
the B value given a new A, using the 3rd order polynomial:

=SUMPRODUCT(X^{3,2,1,0},LINEST(B,A^{1,2,3}))

where X is your unknown A.

--ron

 

[MartinW]

Hi Oligo,

If you are looking for a straight line fit between
data points, try this approach.

Put this formula in D1 and drag it down to D4
=IF(AND($E$1>=A1,$E$1<=A2),TREND(B1:B2,A1:A2,$E$1),0)

In F4 put this
=MAX(D14)

Put a new value for A into E1
and F1 will reflect the corresponding B value.

HTH
Martin

 

[Oligo]

Hi Oligo,

If you are looking for a straight line fit between
data points, try this approach.

Put this formula in D1 and drag it down to D4
=IF(AND($E$1>=A1,$E$1<=A2),TREND(B1:B2,A1:A2,$E$1),0)

In F4 put this
=MAX(D14)

Put a new value for A into E1
and F1 will reflect the corresponding B value.

HTH
Martin

thanks martin. but now i got another issue. your interpolate formula solve
the problem. but i need the interpolated value to be reference in formula on
another worksheet. so now i dont know how to reference to the cell since the
interpolation values will only be adjacent to the cell that match the if
analysis.

 

[MartinW]

Say your interpolated value is in Sheet1 cell F1,
Put this in the other Sheet
=Sheet1!F1

You can reference another sheet like that
in any formula.

HTH
Martin

 

[Oligo]

thanks martin.

Say your interpolated value is in Sheet1 cell F1,
Put this in the other Sheet
=Sheet1!F1

You can reference another sheet like that
in any formula.

HTH
Martin