Interpolation in Excel

[Ruben40870]

Hi All,

I am hoping someone can help me with an issue I have been having. I am trying to add an Interpolation sum in one of my spreadsheets but I just can't figure out how. Below is the sum:

I really hope someone can help.

Thanks in advance!

[#2]

@Ruben40870 

I wrote this spreadsheet for another post but it applies here.

The difference is that the spreadsheet formula looks up the closest points for which data is provided rather than the input being uniquely identified.

[#3]

@Peter Bartholomew Thank you Peter for your reply.

I don't actually need the Lookup function as the data will always change, so I would need to manually enter the data anyway.

I tried using your formula but it doesn't give me the answer I need.

[#4]

It gave the answer you quoted to 6s.f.
That aside, it may go better if you inform the forum of the Excel version you use (my formulation is specific to Excel 365) and ideally provide a workbook to demonstrate how the input is held and the required solution.

[#5]

@Peter Bartholomew I also use Excel 365.

I'll post an image of the table below:

I hope this makes it easier.

[#6]

@Ruben40870 

Ultimately I would aim at using a Lambda function to lookup the Room Cavity Ratios but for now

= LET(
  CU,   XLOOKUP(pw₀, pw, table_CU),
  RCR₁, XLOOKUP(RCR₀,RCR,RCR,,-1),
  RCR₂, XLOOKUP(RCR₀,RCR,RCR,,1),
  CU₁,  XLOOKUP(RCR₀, RCR, CU,,-1),
  CU₂,  XLOOKUP(RCR₀, RCR, CU,,1),
  w₁,  (RCR₂-RCR₀)/(RCR₂-RCR₁),
  w₂,  (RCR₀-RCR₁)/(RCR₂-RCR₁),
  IF(RCR₁<>RCR₀, w₁*CU₁+w₂*CU₂, CU₁) )

should work with the table as far as I have copied it in to Excel.  If you choose to type in the values for  RCR₁, RCR₂, CU₁, CU₂, and remove the XLOOKUPS that should work fine, though I would find it tedious and error-prone.  Do you also need to interpolate across 'pw' or is that a straight lookup?

[#7]

No, the pw only tells you which values to use. I'm basically given a set of data and with this data, I have to find the appropriate values from the table. Then use interpolation to find a more accurate CU value.

[#8]

Do you need me to remove the XLOOKUPS or can you
a) use them, or
b) remove them and link to data cells

[#9]

Remove them and link to data cells. I'll just manually input the data into specific cells.

[#10]

@Ruben40870 

I thought I had given up the process of manual lookups when logarithm tables faded into obsolescence!  In theory I could use those direct cell reference thingies -- but its against my religion.

Joking aside, I hope this is what you want.