Use of Match, Index, and Vlookup to Interpolate

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

velocityinc@gmail.com - 11 Apr 2007 02:53 GMT

I have a 3 column table which looks like the following

A1..5 B1...2.0 C1 8.0
A2..12 B2...3.25 C2 11.0
A3..16 B3...4.65 C3 15.5

I would like to input a value of 7 in cell D1 that does the following:

Output: Looks in column A for 7 between 5 and 12, and then
interpolates between values 2.0 and 3.25 (based on the separation of 7
from 5 and 7 from 12) as the output from column B.

Because the columns are long, all the way to A250, I cannot do this
manually in any efficient way.

Thanks

Reply to this Message

JMB - 11 Apr 2007 06:02 GMT

If the numbers in column A are in ascending order, perhaps

=TREND(INDEX(B1:B3,MATCH(D1,A1:A3)):INDEX(B1:B3,MATCH(D1,A1:A3)+1),INDEX(A1:A3,MATCH(D1,A1:A3)):INDEX(A1:A3,MATCH(D1,A1:A3)+1),A8)

> I have a 3 column table which looks like the following
>
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message

velocityinc@gmail.com - 11 Apr 2007 07:00 GMT

> If the numbers in column A are in ascending order, perhaps
>
[quoted text clipped - 19 lines]
>
> - Show quoted text -

How does the A8 at the end play a part? Just this question, else it
will work like a charm, thanks to your help

Reply to this Message

Lori - 11 Apr 2007 09:26 GMT

Another possibility is:

=PERCENTILE(B1:B3,PERCENTRANK(A1:A3,D1,20))

which works on any ascending dataset as in the example. This works
because the percentile/percentrank functions interpolate for the "in-
between" values.

Or in general use the Trend formula above or this array formula (ctrl
+shift+enter to evaluate):

=FORECAST(D1,IF((MATCH(D1,A:A)-ROW(A1:A3)+0.5)^2<1,B1:B3),A1:A3)

The Forecast and Trend functions use a "best fit" line for estimating
values in the dataset. To intepolate between the points the formula
above just uses the line joining the two nearest points.

On 11 Apr, 07:00, velocity...@gmail.com wrote:

> > If the numbers in column A are in ascending order, perhaps
>
[quoted text clipped - 24 lines]
>
> - Show quoted text -

Reply to this Message

JMB - 12 Apr 2007 01:28 GMT

A8 s/b D1 where D1=7. But I would use one of Lori's suggestions - they're
much shorter.

> > If the numbers in column A are in ascending order, perhaps
> >
[quoted text clipped - 22 lines]
> How does the A8 at the end play a part? Just this question, else it
> will work like a charm, thanks to your help

Reply to this Message