Interpolation formula

**Deladier** · 02-24-2010, 01:56 PM

Hello experts, I have 2 columns (A1:A16 and B1:B16) with the following values:

0 191
2 213
4 236
6 260
8 286
10 313
12 341
14 371
16 402
18 434
20 469
22 505
24 543
26 582
28 623
30 666

In cell D1 I can put any value among 191 to 666 (from Column B), and in cell E1 there is a formula that gives you the interpolation result from Column A, using the formula:

=ROUND(INDEX(A1:A16,MATCH(D1,B1:B16,1))+(D1-INDEX(B1:B16,MATCH(D1,B1:B16,1)))/((INDEX(B1:B16,MATCH(D1,B1:B16,1)+1)-INDEX(B1:B16,MATCH(D1,B1:B16,1)))/2),1)

examples:

if you set 236 value in cell D1, the result in cell E1 will be 4
if you set 410 value in cell D1, the result in cell E1 will be 16.5
etc.

But when you set 666 value in cell D1, the result in cell E1 will be #REF!. In this case, how could you fix this in order to get 30 instead of #REF! ?.

Thanks in advance.

**zbor** · 02-24-2010, 03:01 PM

What version do you use (would be nice to state in profile).

If XL 2007 you can use =IFERROR(formula, 30)

If not you can test first for MAX in value: =IF(D1=MAX(B1:B16,MAX(A1:A16), formula)

**Deladier** · 02-24-2010, 03:25 PM

Ok, I'm using Excel 2003. Thanks, let me try it.

**Deladier** · 02-24-2010, 03:38 PM

Thanks, it works fine.

**daddylonglegs** · 02-25-2010, 08:05 AM

Originally Posted by Deladier

=ROUND(INDEX(A1:A16,MATCH(D1,B1:B16,1))+(D1-INDEX(B1:B16,MATCH(D1,B1:B16,1)))/((INDEX(B1:B16,MATCH(D1,B1:B16,1)+1)-INDEX(B1:B16,MATCH(D1,B1:B16,1)))/2),1)

I note that your formula is based on increments of 2 in A1:A16, if that range doesn't have increments of 2 then your formula won't give the correct results. This formula will allow any increments in that column

=ROUND(IF(D1=B$16,A$16,LOOKUP(D1,B$1:B$15,A$1:A$15+(D1-B$1:B$15)/(B$2:B$16-B$1:B$15)*(A$2:A$16-A$1:A$15))),1)