# Linear Interpolation (2 input, 1 output)

#### Peter Bartholomew

##### Well-Known Member
Code:
``````= LET(
temperatureBand,      XLOOKUP(@Temperature, Data[Temperature], Data[Temperature], ,{-1,1}),
pressureBand,         XLOOKUP(@Temperature, Data[Temperature], selectedPressureCase, ,{-1,1}),
interpolatedPressure, TREND( pressureBand, temperatureBand, @Temperature ),
interpolatedPressure )``````

#### Attachments

• 12.4 KB Views: 7

#### Excel Wizard

##### Member
Shorter but volatile
=TREND(OFFSET(\$B\$2,MATCH(G3,\$B\$3:\$B\$8),MATCH(H3,\$C\$2:\$E\$2,),2),OFFSET(\$B\$2,MATCH(G3,\$B\$3:\$B\$8),,2),G3)
or Non volatile
=TREND(INDEX(LARGE(INDEX(\$C\$3:\$E\$8,,MATCH(H3,\$C\$2:\$E\$2,)),MATCH(G3,\$B\$3:\$B\$8)+{0;1}),),INDEX(SMALL(\$B\$3:\$B\$8,MATCH(G3,\$B\$3:\$B\$8)+{0;1}),),G3)

#### Attachments

• 11.4 KB Views: 10

#### XOR LX

##### Active Member
A shorter alternative for the non-volatile option:

=TREND(LARGE(IF(C\$2:E\$2=H3,C\$3:E\$8),MATCH(G3,B\$3:B\$8)+{0,1}),INDEX(B\$3:B\$8,N(IF(1,MATCH(G3,B\$3:B\$8)+{0,1}))),G3)

Regards

• herofox

#### Peter Bartholomew

##### Well-Known Member
@XOR LX
By the time I had managed to 'simplify' the formula to something I understood, it looked like
Code:
``````= TREND(
INDEX(Data[Temperature], N(IF(1,MATCH(@Temperature, Data[Temperature])+{0,1}))),
@Temperature)``````
which I suppose would cause as much alarm and despondency amongst 'real' spreadsheeters as my original Out of curiosity why was the coercion needed?
Code:
``````= TREND(
INDEX(Data[Temperature], MATCH(@Temperature, Data[Temperature])+{0,1}),
@Temperature)``````
works as well for me.

#### XOR LX

##### Active Member
Out of curiosity why was the coercion needed?
Just because I wasn't assuming access to Office 365 dynamic array functionality, versions prior to which would require the coercion.

Regards

#### Peter Bartholomew

##### Well-Known Member
Fair enough; I am clearly rapidly forgetting the way things used to be! Even worse, it is so many years since I have used direct cell referencing, that I now approach it as a cipher decryption challenge rather than recognising 'normal' code.

#### XOR LX

##### Active Member
If the entries in B3:B8 are fixed at intervals of 100 we can use:

=PERCENTILE(LARGE(IF(C\$2:E\$2=H3,C\$3:E\$8),MATCH(G3,B\$3:B\$8)+{0,1}),1-MOD(G3,100)/100)

Regards

• herofox and akhlsh.kr

#### ahtze

##### New Member
Thank you so much guys~

#### ahtze

##### New Member
There is one issue, when I put the temperature to the MAX (600) in this case, it returns error.

#### XOR LX

##### Active Member
One option:

=AGGREGATE(16,6,LARGE(IF(C\$2:E\$2=H3,C\$3:E\$8),MATCH(G3,B\$3:B\$8)+{0,1}),1-MOD(G3,100)/100)

Regards

• herofox