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

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

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


" wrote:

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

On Apr 11, 1:02 am, JMB wrote:
If the numbers in column A are in ascending order, perhaps

=TREND(INDEX(B1:B3,MATCH(D1,A1:A3)):INDEX(B1:B3,MA TCH(D1,A1:A3)+1),INDEX(A1*:A3,MATCH(D1,A1:A3)):IND EX(A1:A3,MATCH(D1,A1:A3)+1),A8)



" wrote:
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- Hide quoted text -

- 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

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, wrote:
On Apr 11, 1:02 am, JMB wrote:





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

=TREND(INDEX(B1:B3,MATCH(D1,A1:A3)):INDEX(B1:B3,MA TCH(D1,A1:A3)+1),INDEX(A1**:A3,MATCH(D1,A1:A3)):IN DEX(A1:A3,MATCH(D1,A1:A3)+1),A8)

" wrote:
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- Hide quoted text -

- 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- Hide quoted text -

- Show quoted text -

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


" wrote:

On Apr 11, 1:02 am, JMB wrote:
If the numbers in column A are in ascending order, perhaps

=TREND(INDEX(B1:B3,MATCH(D1,A1:A3)):INDEX(B1:B3,MA TCH(D1,A1:A3)+1),INDEX(A1Â*:A3,MATCH(D1,A1:A3)):IN DEX(A1:A3,MATCH(D1,A1:A3)+1),A8)



" wrote:
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- Hide quoted text -

- 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