# Interpolation?

#### Brian from Maui

##### MrExcel MVP
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
ABCD
110.011.012.0
222000040.037.535.0
324000040.037.535.0
426000045.042.540.0
Sheet1

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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))

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))

Would your formula work for 250000 and 10.5?

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

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

Thanks!!!

Replies
16
Views
400
Replies
4
Views
115
Replies
2
Views
188
Replies
0
Views
127
Replies
10
Views
369

1,218,889
Messages
6,145,019
Members
450,586
Latest member
hehehihi2007

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back