Jun 01 2021 01:55 AM
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!
Jun 01 2021 02:41 AM
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.
Jun 01 2021 04:49 AM
@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.
Jun 01 2021 05:52 AM
Jun 01 2021 06:41 AM
@Peter Bartholomew I also use Excel 365.
I'll post an image of the table below:
I hope this makes it easier.
Jun 01 2021 09:04 AM
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?
Jun 01 2021 11:08 AM
Jun 01 2021 11:35 AM
Jun 01 2021 11:47 AM
Jun 01 2021 01:39 PM
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.