Bilinear interpolation on Excel without using macro

[mad_hans]

Hello all,

I just wanted to stop by and share my work. I needed to do a bilinear interpolation of Thermal Conductivity from a 2D table of temperatures(columns) and pressures(lines) -> k(P,T). I have a specific point of temperature and pressure and need the thermal conductivity interpolated from them. After extensive googling, I saw no one had a good solution for this. So I implemented it myself and want to share for posterity.
I have three data ranges setup
- temp (the line with the temperature values from my reference table)
- press (the column with the pressure values from my reference table)
- conductivity (the 2D array of conductivity data)

The formula below searches for the reference temperatures and pressures below and above the desired (P,T), interpolates between them. T is on A1 and P is on B1

The algorithm I took from wikipedia (https://en.wikipedia.org/wiki/Bilinear_interpolation)

=(INDEX(conductivity;(MATCH(B1;press;1));(MATCH(A1;temp;1)))*(1-(A1-INDEX(temp;1;(MATCH(A1;temp;1))))/(INDEX(temp;1;(MATCH(A1;temp;1)+1))-INDEX(temp;1;(MATCH(A1;temp;1)))))+INDEX(conductivity;(MATCH(B1;press;1));(MATCH(A1;temp;1)+1))*(A1-INDEX(temp;1;(MATCH(A1;temp;1))))/(INDEX(temp;1;(MATCH(A1;temp;1)+1))-INDEX(temp;1;(MATCH(A1;temp;1)))))*(1-((B1-INDEX(press;(MATCH(B1;press;1));1))/(INDEX(press;(MATCH(B1;press;1)+1);1)-INDEX(press;(MATCH(B1;press;1));1))))+(INDEX(conductivity;(MATCH(B1;press;1)+1);(MATCH(A1;temp;1)))*(1-(A1-INDEX(temp;1;(MATCH(A1;temp;1))))/(INDEX(temp;1;(MATCH(A1;temp;1)+1))-INDEX(temp;1;(MATCH(A1;temp;1)))))+INDEX(conductivity;(MATCH(B1;press;1)+1);(MATCH(A1;temp;1)+1))*(A1-INDEX(temp;1;(MATCH(A1;temp;1))))/(INDEX(temp;1;(MATCH(A1;temp;1)+1))-INDEX(temp;1;(MATCH(A1;temp;1)))))*((B1-INDEX(press;(MATCH(B1;press;1));1))/(INDEX(press;(MATCH(B1;press;1)+1);1)-INDEX(press;(MATCH(B1;press;1));1)))

You are now free to interpolate as needed !!!!

Trilinear interpolation just requires doing this twice and then interpolating between the results, in case you are asking

Hans