Linear Interpolation
 

Hello,

I am trying to create a formula that will lookup values in a column and find
those values in another column and then place the value in the row next to it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value for cell I6
(thru I n) will not be found in the list ($D$11:$E$223) so I want to revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match is found in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find the values
less than and greater than I6 and interpolate between the two points a value
based on the slope of the function defined by the two sets of corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years of data to
enter and if I do it manually it will take 20 years.

Linear Interpolation
 

http://groups.google.co.uk/groups?as...=2005&safe=off

That should all be one line in your browser navigation window.

--
Regards,
Tom Ogilvy



"Metalmaniac" wrote in message
...
Hello,

I am trying to create a formula that will lookup values in a column and
find
those values in another column and then place the value in the row next to
it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value for cell I6
(thru I n) will not be found in the list ($D$11:$E$223) so I want to
revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match is found
in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find the values
less than and greater than I6 and interpolate between the two points a
value
based on the slope of the function defined by the two sets of
corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years of data to
enter and if I do it manually it will take 20 years.

Linear Interpolation
 

In order to come up with the interpolation formula (the easy way) just graph
the source data and add a linear trend line. Right click the trend line and
one of the options is to add the formula to the trend line. This will give
you the formula of the best line through all of the points on the graph. If
you wanted to interpolate between any two point look at the slope() and
intercept() formulas. Using these you should be able to create the Y=aX + b
formulas for each set of points.
--
HTH...

Jim Thomlinson


"Metalmaniac" wrote:

Hello,

I am trying to create a formula that will lookup values in a column and find
those values in another column and then place the value in the row next to it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value for cell I6
(thru I n) will not be found in the list ($D$11:$E$223) so I want to revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match is found in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find the values
less than and greater than I6 and interpolate between the two points a value
based on the slope of the function defined by the two sets of corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years of data to
enter and if I do it manually it will take 20 years.

Linear Interpolation
 

Just a thought, but
that wouldn't work for the situation described/asked for unless the data was
linear and fell exactly on the trend line.



--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote in message
...
In order to come up with the interpolation formula (the easy way) just
graph
the source data and add a linear trend line. Right click the trend line
and
one of the options is to add the formula to the trend line. This will give
you the formula of the best line through all of the points on the graph.
If
you wanted to interpolate between any two point look at the slope() and
intercept() formulas. Using these you should be able to create the Y=aX +
b
formulas for each set of points.
--
HTH...

Jim Thomlinson


"Metalmaniac" wrote:

Hello,

I am trying to create a formula that will lookup values in a column and
find
those values in another column and then place the value in the row next
to it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value for cell
I6
(thru I n) will not be found in the list ($D$11:$E$223) so I want to
revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match is found
in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find the
values
less than and greater than I6 and interpolate between the two points a
value
based on the slope of the function defined by the two sets of
corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years of data
to
enter and if I do it manually it will take 20 years.

Linear Interpolation
 

Agreed on the trend line part. It does assume that the formula of the best
line is desired. This is why I gave the Slope and Intercept formulas which
can be used on any two points. Using these formulas a linear formula can be
created for each point to the next point in the series. This will require
more calculations but it will work. Depends on what the user wants. I have
done something similar using both linear and polynomial regression (Trend
Formula) with success.
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

Just a thought, but
that wouldn't work for the situation described/asked for unless the data was
linear and fell exactly on the trend line.



--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote in message
...
In order to come up with the interpolation formula (the easy way) just
graph
the source data and add a linear trend line. Right click the trend line
and
one of the options is to add the formula to the trend line. This will give
you the formula of the best line through all of the points on the graph.
If
you wanted to interpolate between any two point look at the slope() and
intercept() formulas. Using these you should be able to create the Y=aX +
b
formulas for each set of points.
--
HTH...

Jim Thomlinson


"Metalmaniac" wrote:

Hello,

I am trying to create a formula that will lookup values in a column and
find
those values in another column and then place the value in the row next
to it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value for cell
I6
(thru I n) will not be found in the list ($D$11:$E$223) so I want to
revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match is found
in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find the
values
less than and greater than I6 and interpolate between the two points a
value
based on the slope of the function defined by the two sets of
corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years of data
to
enter and if I do it manually it will take 20 years.

Linear Interpolation
 

If the OP wants a linear interpolation between just two points in his data
as stated, unless his data falls exactly on his trendline, then using the
formula for the trend line will not work (the formula for the trendline is a
best fit for all the data points vice the two points being addressed -
unless the special case that the data falls exactly on the trendline). No
doubt you have had repeated success in all your endeavors, but no matter how
well made, a left shoe doesn't fit a right foot.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote in message
...
Agreed on the trend line part. It does assume that the formula of the best
line is desired. This is why I gave the Slope and Intercept formulas which
can be used on any two points. Using these formulas a linear formula can
be
created for each point to the next point in the series. This will require
more calculations but it will work. Depends on what the user wants. I have
done something similar using both linear and polynomial regression (Trend
Formula) with success.
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

Just a thought, but
that wouldn't work for the situation described/asked for unless the data
was
linear and fell exactly on the trend line.



--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote in message
...
In order to come up with the interpolation formula (the easy way) just
graph
the source data and add a linear trend line. Right click the trend
line
and
one of the options is to add the formula to the trend line. This will
give
you the formula of the best line through all of the points on the
graph.
If
you wanted to interpolate between any two point look at the slope()
and
intercept() formulas. Using these you should be able to create the
Y=aX +
b
formulas for each set of points.
--
HTH...

Jim Thomlinson


"Metalmaniac" wrote:

Hello,

I am trying to create a formula that will lookup values in a column
and
find
those values in another column and then place the value in the row
next
to it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value for
cell
I6
(thru I n) will not be found in the list ($D$11:$E$223) so I want to
revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match is
found
in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find the
values
less than and greater than I6 and interpolate between the two points
a
value
based on the slope of the function defined by the two sets of
corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years of
data
to
enter and if I do it manually it will take 20 years.

Linear Interpolation
 

"repeated success in all your endeavors" seems to be an overly optomistic
evaluation of my Excel endeavors, but if you insist. :-) I was just giving
two alternatives to Metalmaniac which I thought was identified in my original
reply. Which one will work best is up to him. I gave the trend line solution
because of his statement "based on the slope of the function defined by the
two sets of coresponding data".
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

If the OP wants a linear interpolation between just two points in his data
as stated, unless his data falls exactly on his trendline, then using the
formula for the trend line will not work (the formula for the trendline is a
best fit for all the data points vice the two points being addressed -
unless the special case that the data falls exactly on the trendline). No
doubt you have had repeated success in all your endeavors, but no matter how
well made, a left shoe doesn't fit a right foot.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote in message
...
Agreed on the trend line part. It does assume that the formula of the best
line is desired. This is why I gave the Slope and Intercept formulas which
can be used on any two points. Using these formulas a linear formula can
be
created for each point to the next point in the series. This will require
more calculations but it will work. Depends on what the user wants. I have
done something similar using both linear and polynomial regression (Trend
Formula) with success.
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

Just a thought, but
that wouldn't work for the situation described/asked for unless the data
was
linear and fell exactly on the trend line.



--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote in message
...
In order to come up with the interpolation formula (the easy way) just
graph
the source data and add a linear trend line. Right click the trend
line
and
one of the options is to add the formula to the trend line. This will
give
you the formula of the best line through all of the points on the
graph.
If
you wanted to interpolate between any two point look at the slope()
and
intercept() formulas. Using these you should be able to create the
Y=aX +
b
formulas for each set of points.
--
HTH...

Jim Thomlinson


"Metalmaniac" wrote:

Hello,

I am trying to create a formula that will lookup values in a column
and
find
those values in another column and then place the value in the row
next
to it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value for
cell
I6
(thru I n) will not be found in the list ($D$11:$E$223) so I want to
revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match is
found
in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find the
values
less than and greater than I6 and interpolate between the two points
a
value
based on the slope of the function defined by the two sets of
corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years of
data
to
enter and if I do it manually it will take 20 years.

Linear Interpolation
 

My comments were only aimed at the use of the Trend line in a chart method.

Slope and intercept are useful, but personally I would look at Forecast or
Trend in this case.

but in any event, identifying the X /Y values to interpolate are more the
challenge in using any of them. Those methods should be contained/discussed
in the link provided.

--
Regards,
Tom Ogilvy

"Jim Thomlinson" wrote in message
...
"repeated success in all your endeavors" seems to be an overly optomistic
evaluation of my Excel endeavors, but if you insist. :-) I was just
giving
two alternatives to Metalmaniac which I thought was identified in my
original
reply. Which one will work best is up to him. I gave the trend line
solution
because of his statement "based on the slope of the function defined by
the
two sets of coresponding data".
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

If the OP wants a linear interpolation between just two points in his
data
as stated, unless his data falls exactly on his trendline, then using
the
formula for the trend line will not work (the formula for the trendline
is a
best fit for all the data points vice the two points being addressed -
unless the special case that the data falls exactly on the trendline).
No
doubt you have had repeated success in all your endeavors, but no matter
how
well made, a left shoe doesn't fit a right foot.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote in message
...
Agreed on the trend line part. It does assume that the formula of the
best
line is desired. This is why I gave the Slope and Intercept formulas
which
can be used on any two points. Using these formulas a linear formula
can
be
created for each point to the next point in the series. This will
require
more calculations but it will work. Depends on what the user wants. I
have
done something similar using both linear and polynomial regression
(Trend
Formula) with success.
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

Just a thought, but
that wouldn't work for the situation described/asked for unless the
data
was
linear and fell exactly on the trend line.



--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote in message
...
In order to come up with the interpolation formula (the easy way)
just
graph
the source data and add a linear trend line. Right click the trend
line
and
one of the options is to add the formula to the trend line. This
will
give
you the formula of the best line through all of the points on the
graph.
If
you wanted to interpolate between any two point look at the
slope()
and
intercept() formulas. Using these you should be able to create the
Y=aX +
b
formulas for each set of points.
--
HTH...

Jim Thomlinson


"Metalmaniac" wrote:

Hello,

I am trying to create a formula that will lookup values in a
column
and
find
those values in another column and then place the value in the
row
next
to it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value
for
cell
I6
(thru I n) will not be found in the list ($D$11:$E$223) so I
want to
revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match
is
found
in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find
the
values
less than and greater than I6 and interpolate between the two
points
a
value
based on the slope of the function defined by the two sets of
corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years
of
data
to
enter and if I do it manually it will take 20 years.

Linear Interpolation
 

As always it has been a pleasure butting propellors with you but I think we
have probably beaten this horse to death by now. Trend, Forecast, Trend Line,
Slope and Intercept. If that doesn't cover it nothing will. One day we will
have to get together and argue in person.
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

My comments were only aimed at the use of the Trend line in a chart method.

Slope and intercept are useful, but personally I would look at Forecast or
Trend in this case.

but in any event, identifying the X /Y values to interpolate are more the
challenge in using any of them. Those methods should be contained/discussed
in the link provided.

--
Regards,
Tom Ogilvy

"Jim Thomlinson" wrote in message
...
"repeated success in all your endeavors" seems to be an overly optomistic
evaluation of my Excel endeavors, but if you insist. :-) I was just
giving
two alternatives to Metalmaniac which I thought was identified in my
original
reply. Which one will work best is up to him. I gave the trend line
solution
because of his statement "based on the slope of the function defined by
the
two sets of coresponding data".
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

If the OP wants a linear interpolation between just two points in his
data
as stated, unless his data falls exactly on his trendline, then using
the
formula for the trend line will not work (the formula for the trendline
is a
best fit for all the data points vice the two points being addressed -
unless the special case that the data falls exactly on the trendline).
No
doubt you have had repeated success in all your endeavors, but no matter
how
well made, a left shoe doesn't fit a right foot.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote in message
...
Agreed on the trend line part. It does assume that the formula of the
best
line is desired. This is why I gave the Slope and Intercept formulas
which
can be used on any two points. Using these formulas a linear formula
can
be
created for each point to the next point in the series. This will
require
more calculations but it will work. Depends on what the user wants. I
have
done something similar using both linear and polynomial regression
(Trend
Formula) with success.
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

Just a thought, but
that wouldn't work for the situation described/asked for unless the
data
was
linear and fell exactly on the trend line.



--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote in message
...
In order to come up with the interpolation formula (the easy way)
just
graph
the source data and add a linear trend line. Right click the trend
line
and
one of the options is to add the formula to the trend line. This
will
give
you the formula of the best line through all of the points on the
graph.
If
you wanted to interpolate between any two point look at the
slope()
and
intercept() formulas. Using these you should be able to create the
Y=aX +
b
formulas for each set of points.
--
HTH...

Jim Thomlinson


"Metalmaniac" wrote:

Hello,

I am trying to create a formula that will lookup values in a
column
and
find
those values in another column and then place the value in the
row
next
to it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value
for
cell
I6
(thru I n) will not be found in the list ($D$11:$E$223) so I
want to
revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match
is
found
in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find
the
values
less than and greater than I6 and interpolate between the two
points
a
value
based on the slope of the function defined by the two sets of
corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years
of
data
to
enter and if I do it manually it will take 20 years.

Linear Interpolation
 

I didn't go through all the hits in the google archive (the link Tom
provided) but using a formula for a general purpose solution is kinda
messy. By contrast a user defined function (UDF) might be a lot
cleaner. And, it can written to handle multiple a vector of input
values and return a vector of results with a single call!

In any case, if you want to stick with formulas, you would need to do
the following. Suppose the x and y data are in columns A and B
starting with row 1 (i.e., no header).

Then, define the following names (Insert | Names Define...):
XVals =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
YVals =OFFSET(XVals,,1)
XMax =INDEX(XVals,COUNTA(Sheet1!$A:$A))
XMin =INDEX(XVals,1)
YMax =OFFSET(XMax,,1)
YMin =OFFSET(XMin,,1)

Now, if you want the interpolated y value corresponding to the x value
in D2, use

=IF(OR(D2<XMin,D2XMax),"out of bounds",IF(ABS(D2-XMax)
<0.00000001,YMax,FORECAST(D2,OFFSET(YMin,MATCH(D2, XVals,1)-
1,0,2,1),OFFSET(XMin,MATCH(D2,XVals,1)-1,0,2,1))))

I wrote a UDF for linear interpolation some time back. I will try and
find it, but no promises.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello,

I am trying to create a formula that will lookup values in a column and find
those values in another column and then place the value in the row next to it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value for cell I6
(thru I n) will not be found in the list ($D$11:$E$223) so I want to revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match is found in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find the values
less than and greater than I6 and interpolate between the two points a value
based on the slope of the function defined by the two sets of corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years of data to
enter and if I do it manually it will take 20 years.