Compare & Interpolate Values

  • Thread starter Thread starter Milia
  • Start date Start date
M

Milia

Table with two dependent columns (example. Height1 vs. Capacity1).
Now I want to create a new, two column table with a given Capacity,
lets call it Capacity2).

So these are my givens:

Height 1 Capacity 1
10 500
20 600
30 900

Capacity 2 Height 2
550
610
850

Now, I want to create a function with will take my Capacity 2 value,
compare it with Capacity 1 and interpolate the appropriate Height 2
value (from Height 1).
 
Milia,

Set up your table so that it is the reverse of what you have:

Capacity 1 Height 1
500 10
600 20
900 30

Then put the volumes into cells A7:A9, (the 550). Then use this
formula in B7 (remove any spurious line-breaks thrown in by our mail
readers)

=INDEX($A$2:$B$4,MATCH(A7,$A$2:$A$4),2)+(INDEX($A$2:$B$4,MATCH(A7,$A$2
:$A$4)+1,2)-INDEX($A$2:$B$4,MATCH(A7,$A$2:$A$4),2))*((A7-INDEX($A$2:$B
$4,MATCH(A7,$A$2:$A$4),1))/(INDEX($A$2:$B$4,MATCH(A7,$A$2:$A$4)+1,1)-I
NDEX($A$2:$B$4,MATCH(A7,$A$2:$A$4),1)))

and copy to B8 and B9.

HTH,
Bernie
MS Excel MVP
 
Back
Top