 # Interpolation in Excel

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.

9 Replies

# Re: Interpolation in Excel

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. # Re: Interpolation in Excel

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.

# Re: Interpolation in Excel

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.

# Re: Interpolation in Excel

@Peter Bartholomew I also use Excel 365.

I'll post an image of the table below: I hope this makes it easier.

# Re: Interpolation in Excel

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?

# Re: Interpolation in Excel

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.

# Re: Interpolation in Excel

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

# Re: Interpolation in Excel

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

# Re: Interpolation in Excel

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.