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.

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" <Metalmaniac@discussions.microsoft.com> wrote in message
news:4B74742A-5CD9-4757-857E-127268883D2F@microsoft.com...
> 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.

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.

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" <jamest@tcgiRe-Move-This.com> wrote in message
news:0F693996-79A9-4E94-BC79-B553BB5DBA73@microsoft.com...
> 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.

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" <jamest@tcgiRe-Move-This.com> wrote in message
> news:0F693996-79A9-4E94-BC79-B553BB5DBA73@microsoft.com...
> > 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.
>
>
>

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" <jamest@tcgiRe-Move-This.com> wrote in message
news:A93BB000-B291-4BF9-BC6C-AC3BF92304BB@microsoft.com...
> 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" <jamest@tcgiRe-Move-This.com> wrote in message
> > news:0F693996-79A9-4E94-BC79-B553BB5DBA73@microsoft.com...
> > > 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.
> >
> >
> >

"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" <jamest@tcgiRe-Move-This.com> wrote in message
> news:A93BB000-B291-4BF9-BC6C-AC3BF92304BB@microsoft.com...
> > 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" <jamest@tcgiRe-Move-This.com> wrote in message
> > > news:0F693996-79A9-4E94-BC79-B553BB5DBA73@microsoft.com...
> > > > 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.
> > >
> > >
> > >
>
>
>

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" <jamest@tcgiRe-Move-This.com> wrote in message
news:B62CDB69-91B9-442C-8C10-1C01746380B5@microsoft.com...
> "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" <jamest@tcgiRe-Move-This.com> wrote in message
> > news:A93BB000-B291-4BF9-BC6C-AC3BF92304BB@microsoft.com...
> > > 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" <jamest@tcgiRe-Move-This.com> wrote in message
> > > > news:0F693996-79A9-4E94-BC79-B553BB5DBA73@microsoft.com...
> > > > > 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.
> > > >
> > > >
> > > >
> >
> >
> >

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" <jamest@tcgiRe-Move-This.com> wrote in message
> news:B62CDB69-91B9-442C-8C10-1C01746380B5@microsoft.com...
> > "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" <jamest@tcgiRe-Move-This.com> wrote in message
> > > news:A93BB000-B291-4BF9-BC6C-AC3BF92304BB@microsoft.com...
> > > > 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" <jamest@tcgiRe-Move-This.com> wrote in message
> > > > > news:0F693996-79A9-4E94-BC79-B553BB5DBA73@microsoft.com...
> > > > > > 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.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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,D2>XMax),"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 <4B74742A-5CD9-4757-857E-127268883D2F@microsoft.com>,
Metalmaniac@discussions.microsoft.com 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.
>