• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Linear Interpolation (2 input, 1 output)

ahtze

New Member
Please help to create formula or VBA to do linear interpolation between two values and depending on which cases?

71316
 

Attachments

  • Interpolation help.xlsx
    10.2 KB · Views: 7
Code:
= LET(
  selectedPressureCase, XLOOKUP(@PressureCase, Data[#Headers], Data ),
  temperatureBand,      XLOOKUP(@Temperature, Data[Temperature], Data[Temperature], ,{-1,1}),
  pressureBand,         XLOOKUP(@Temperature, Data[Temperature], selectedPressureCase, ,{-1,1}),
  interpolatedPressure, TREND( pressureBand, temperatureBand, @Temperature ),
  interpolatedPressure )
This applies to recent versions of Microsoft365
 

Attachments

  • PressureInterpolation.xlsx
    12.4 KB · Views: 7
Please try
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

  • Interpolation help.xlsx
    11.4 KB · Views: 12
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
 
@XOR LX
By the time I had managed to 'simplify' the formula to something I understood, it looked like
Code:
= TREND(
    LARGE(IF(Data[#Headers]=@PressureCase,Data), MATCH(@Temperature,Data[Temperature])+{0,1}),
    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(
    LARGE(IF(Data[#Headers]=@PressureCase,Data), MATCH(@Temperature,Data[Temperature])+{0,1}),
    INDEX(Data[Temperature], MATCH(@Temperature, Data[Temperature])+{0,1}),
    @Temperature)
works as well for me.
 
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.
 
There is one issue, when I put the temperature to the MAX (600) in this case, it returns error.
 
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
 
Back
Top