Interpolation?

[Brian from Maui]

From the table below, if I had known values of 230000 and 10.5, how can I come up with a value of 38.75?

Book2
	A	B	C	D
1		10.0	11.0	12.0
2	220000	40.0	37.5	35.0
3	240000	40.0	37.5	35.0
4	260000	45.0	42.5	40.0
Sheet1

 

[Andrew Poulsom]

Assuming 230000 is in cell A6 and 10.5 is in cell B5:

=AVERAGE(INDEX(B2:D4,MATCH(A6,A2:A4,1),MATCH(B5,B1:D1,1)),INDEX(B2:D4,MATCH(A6,A2:A4,1)+1,MATCH(B5,B1:D1,1)),INDEX(B2:D4,MATCH(A6,A2:A4,1),MATCH(B5,B1:D1,1)+1),INDEX(B2:D4,MATCH(A6,A2:A4,1)+1,MATCH(B5,B1:D1,1)+1))

 

[Aladin Akyurek]

On 2002-09-08 00:51, Andrew Poulsom wrote:
Assuming 230000 is in cell A6 and 10.5 is in cell B5:

=AVERAGE(INDEX(B2:D4,MATCH(A6,A2:A4,1),MATCH(B5,B1:D1,1)),INDEX(B2:D4,MATCH(A6,A2:A4,1)+1,MATCH(B5,B1:D1,1)),INDEX(B2:D4,MATCH(A6,A2:A4,1),MATCH(B5,B1:D1,1)+1),INDEX(B2:D4,MATCH(A6,A2:A4,1)+1,MATCH(B5,B1:D1,1)+1))

Andrew,

That's great. I believe it can be shortened to:

=AVERAGE(INDEX(B2:D4,MATCH(A6,A2:A4),MATCH(B5,B1:D1)),INDEX(B2:D4,MATCH(A6,A2:A4)+1,MATCH(B5,B1:D1)+1))

Aladin

 

[Andrew Poulsom]

Aladin,

Would your formula work for 250000 and 10.5?

 

[Aladin Akyurek]

On 2002-09-08 01:29, Andrew Poulsom wrote:
Aladin,

Would your formula work for 250000 and 10.5?

Results in 41.25 like yours.

Addendum. We need to add some control to the formula for values outside the table:

=IF(AND(B5>=B1,B5<=D1,A6>=A2,A6<=A4),AVERAGE(INDEX(B2:D4,MATCH(A6,A2:A4),MATCH(B5,B1:D1)),INDEX(B2:D4,MATCH(A6,A2:A4)+1,MATCH(B5,B1:D1)+1)),"NoValue")
This message was edited by aladin akyurek on 2002-09-08 06:28

 

[Brian from Maui]

Andrew and Aladin,

Thanks!!!