## Interpolate Help

## Interpolate Help

(OP)

In Excel, I want to subtract cell J20- cell A14, and lets call this variable W. W correlates with the column N data from SET1. I want to interpolate column M for SET1. W correlates with the column N data from SET2. I want to interpolate column M for SET2. Let's call the results X1 and X2 for SET1 and SET2, respectfully. Then I want to solve X2-X1.

SET1 and SET2 represent the coordinates of the left and right sides of an object, and I want to calculate the horizontal distance at a certain vertical point.

cell J20 = 54

cell A14 =5

SET1 cells M15:N21:

3 54.000

0 50.000

1 50.000

1 44.000

0 44.000

0 24.000

0 0.000

SET 2 cells M29:N33:

17 27.000

14 33.000

11 48.000

11 54.000

3 54.000

I have tried this formula:

=INDEX(M29:M33,MATCH(J20-A14,N29:N33,1)) - INDEX(M15:M21,MATCH(J20-A14,N15:N21,1))

The result should be 10, but I keep getting 11. Any ideas?

SET1 and SET2 represent the coordinates of the left and right sides of an object, and I want to calculate the horizontal distance at a certain vertical point.

cell J20 = 54

cell A14 =5

SET1 cells M15:N21:

3 54.000

0 50.000

1 50.000

1 44.000

0 44.000

0 24.000

0 0.000

SET 2 cells M29:N33:

17 27.000

14 33.000

11 48.000

11 54.000

3 54.000

I have tried this formula:

=INDEX(M29:M33,MATCH(J20-A14,N29:N33,1)) - INDEX(M15:M21,MATCH(J20-A14,N15:N21,1))

The result should be 10, but I keep getting 11. Any ideas?

## RE: Interpolate Help

Your set 1 N values are descending, while set 2 is ascending.

Therefore your second MATCH ought to have a -1 Match Type.

Tip 2: with formula problems, test from the inside out.

I started by looking at the MATCHs as stand alone functions with the given values and ranges. BINGO!

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Interpolate Help

## CODE

## RE: Interpolate Help

## RE: Interpolate Help

You might select a set-value using Match Type +/- 1 but that's early in the process of interpolation.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Interpolate Help

=FORECAST(V19,INDEX(M27:M33,MATCH(V19,N27:N33,1)):INDEX(M27:M33,MATCH(V19,N27:N33,1)+1),INDEX(N27:N33,MATCH(V19,N27:N33,1)):INDEX(N27:N33,MATCH(V19,N27:N33,1)+1))

Gosh, you would think Microsoft would have an easier way to do this.

## RE: Interpolate Help

## CODE -->

## RE: Interpolate Help

Checks spreadsheet library.

There it is; glad you found it useful

That code and related stuff can be found at:

https://newtonexcelbach.com/2012/10/02/daily-downl...

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/