Interpolation from Multiple Data Points, IFTHEN?

Short but useless answer -- yes, there is a way (probably more than one) for doing multivariate interpolation in Excel or any programming language. I would note that I don't usually use IF() functions when I do interpolations.

Naturally, multivariate interpolation gets more complex than simple single variable interpolation. There is probably some value in reviewing the different algorithms for multivariate interpolation to fix ideas: https://en.wikipedia.org/wiki/Multiv..._interpolation

A typical interpolation algorithm in Excel tends to include:

1) identify desired input and output variables
2) Lookup functions (I usually use INDEX() and MATCH() functions) to locate the data in the table.
INDEX() function: https://support.office.com/en-us/art...2-b56b061328bd
MATCH() function: https://support.office.com/en-us/art...9-533f4a37673a
2a) I find that I almost always use binary search/approximate match option in the lookup function (1 or -1 in the 3rd argument of the MATCH() function) so make sure you are familiar with how that option works.
2b) It is often valuable to think through your source data table to make sure it is easy to find stuff in. For example, in your source table, you have both elevation and some other value intermingled across the top. This will be difficult to search, so I could see value in separating elevation from the other value -- possibly even requiring separating the one table into 2 separate tables.
3) Once you have located the "block" of data needed for the interpolation, a series of INDEX() functions to extract those values from the table.
4) With the needed data extrapolated from the table, input those values into the desired interpolating equation. It is not clear to me what that equation should look like for your data, but I assume you have that information.

From what I see, it seems that the lookup step (2) is the one that causes most people the most difficulty.

With the problem broken down into steps, what part do you have trouble with?

Originally Posted by MrShorty

Short but useless answer -- yes, there is a way (probably more than one) for doing multivariate interpolation in Excel or any programming language. I would note that I don't usually use IF() functions when I do interpolations.

Naturally, multivariate interpolation gets more complex than simple single variable interpolation. There is probably some value in reviewing the different algorithms for multivariate interpolation to fix ideas: https://en.wikipedia.org/wiki/Multiv..._interpolation

A typical interpolation algorithm in Excel tends to include:

1) identify desired input and output variables
2) Lookup functions (I usually use INDEX() and MATCH() functions) to locate the data in the table.
INDEX() function: https://support.office.com/en-us/art...2-b56b061328bd
MATCH() function: https://support.office.com/en-us/art...9-533f4a37673a
2a) I find that I almost always use binary search/approximate match option in the lookup function (1 or -1 in the 3rd argument of the MATCH() function) so make sure you are familiar with how that option works.
2b) It is often valuable to think through your source data table to make sure it is easy to find stuff in. For example, in your source table, you have both elevation and some other value intermingled across the top. This will be difficult to search, so I could see value in separating elevation from the other value -- possibly even requiring separating the one table into 2 separate tables.
3) Once you have located the "block" of data needed for the interpolation, a series of INDEX() functions to extract those values from the table.
4) With the needed data extrapolated from the table, input those values into the desired interpolating equation. It is not clear to me what that equation should look like for your data, but I assume you have that information.

From what I see, it seems that the lookup step (2) is the one that causes most people the most difficulty.

With the problem broken down into steps, what part do you have trouble with?

Thanks for the help. In viewing the videos, it seem those functions will help, partially. The issue is I"m not wanting to find values that only correspond to 1600,1900 or 2200 or the 0,15, or 30. The formula would be a combinations of the index and match function, I think, so if I want to know the Ground Roll for 1800 (a value from somehwere between I58 and I61). In the same way the formulas are set up now for IAS (J58:J64), I can input a specific weight and it will return the correst IAS.

The elevation is at the top and the other number beside that is temperature. The ground roll and [distance] to clear 50' [obstacle] would be dependent on weight, wind, altitude and temperature.

Here's a excel version from the Google Doc: https://www.excelforum.com/attachmen...1&d=1513921897

I'm just rambling, now, maybe some of that made sense.

I appreciate your feedback.

The ground roll and [distance] to clear 50' [obstacle] would be dependent on weight, wind, altitude and temperature.

So there are 4 input variables and 2 output variables for the algorithm. I could see some possible value in separating out the two output variables into separate tables, as this might make the lookups easier. Remember that you have 1E6 x 16e3 cells to use (in a single tab, not to mention as many tabs as your machine's memory can store), so there should be little to no concern by having two tables instead of one. As indicated, I think you are going to want to do something different with the elevation and temperature values, because, with them intermingled on the same row, it will be difficult for the lookup functions to locate a value within those ranges. Perhaps arrange these numbers in two rows at the top of the table (like you have the other 2 input variables in separate columns at the left of the table).

The issue is I"m not wanting to find values that only correspond to 1600,1900 or 2200 or the 0,15, or 30.

Correct. The point I am making here is that, along the way to calculating the value at 1800 (or whatever intermediate value you want), you must first locate and extract the exact values that correspond to 1600 and 1900 (or whatever the tabulated values that bracket your desired value). As I indicated, the lookup step is often the difficult step here. Do you understand how to use the MATCH() function to locate 1800 within the values in I58:I66? Do you understand how to follow up the MATCH() function with the necessary INDEX() functions to extract 1600 and 1900 from the table?

So there are 4 input variables and 2 output variables for the algorithm. I could see some possible value in separating out the two output variables into separate tables, as this might make the lookups easier. Remember that you have 1E6 x 16e3 cells to use (in a single tab, not to mention as many tabs as your machine's memory can store), so there should be little to no concern by having two tables instead of one. As indicated, I think you are going to want to do something different with the elevation and temperature values, because, with them intermingled on the same row, it will be difficult for the lookup functions to locate a value within those ranges. Perhaps arrange these numbers in two rows at the top of the table (like you have the other 2 input variables in separate columns at the left of the table)

That's correct. Inputs would be weight, wind, elevation and temperature. 2 outputs would be ground roll and 50' obs clearance. This table was just a direct input copy of what I found in my book. What would be the best way to change things around to make the appropriate function work best? I don't mind if the table is off to the side. If I were to move the altitude and temperatures, could the forumla still "know" what output variables those coincide to?
The altitude and temp directly correspond to the data below those two side-by-side sets of columns.

In the end this is just "extra" for me to want to calculate.

Correct. The point I am making here is that, along the way to calculating the value at 1800 (or whatever intermediate value you want), you must first locate and extract the exact values that correspond to 1600 and 1900 (or whatever the tabulated values that bracket your desired value). As I indicated, the lookup step is often the difficult step here. Do you understand how to use the MATCH() function to locate 1800 within the values in I58:I66? Do you understand how to follow up the MATCH() function with the necessary INDEX() functions to extract 1600 and 1900 from the table?

It seems that would all work similar to what I've done to calculating the interpolation of IAS for an intermediate value of weight - 1800 or otherwise. I am unfamiliar with match function and most advanced functions within google sheets and Excel - that's the issue here is I don't know what I don't know but I appreciate your help so far.

I am unfamiliar with match function and most advanced functions within google sheets and Excel - that's the issue here is I don't know what I don't know but I appreciate your help so far.

The reason I linked to the Excel help files (Google has similar help files, though I expect these function to behave the same in both spreadsheets) is so you can study the documentation. Then put something like =MATCH(1800,$I$58:$I$66,1) into a cell (or put the 1800 into a cell and use a reference to that cell, so you can easily change that value) and see what you get out of the function. Then add another cell with a function like =INDEX($I$58:$S$66,reference to cell with MATCH() function,1) and see what you get from that cell.

I'm out for a bit, but I will check on your progress later.

Originally Posted by MrShorty

The reason I linked to the Excel help files (Google has similar help files, though I expect these function to behave the same in both spreadsheets) is so you can study the documentation. Then put something like =MATCH(1800,$I$58:$I$66,1) into a cell (or put the 1800 into a cell and use a reference to that cell, so you can easily change that value) and see what you get out of the function. Then add another cell with a function like =INDEX($I$58:$S$66,reference to cell with MATCH() function,1) and see what you get from that cell.

I'm out for a bit, but I will check on your progress later.

Will mess around for the next little while and see what I come up with. THank you

Edit +2 hrs: After a few hours, I have rearragned the columns and rows to what I think might be easier. Now, I'm at a lost of what functions to use and where to get the right outcome. Most of the messing around is in Q85:Q90. I can get some things to work but once I have to rely on another input point, things don't match up.

A clarification: Are altitude and temperature truly separate inputs, or are they the same input expressed differently? From your flat list table, 0 ft elevation always goes with T=59 and so on down the list, so that it looks like you would enter either elevation or temperature, but not both. Is that true?

If that is true, then this may reduce to a trilinear interpolation problem, and I just happen to have saved a nice example by user shg showing trilinear interpolation: https://www.excelforum.com/excel-for...ml#post4757864 A few things to draw your attention to:

1) Note the arrangement of the table, with one input variable down the left side and two of the input variables in separate rows across the top. In this case, the OP stored two values as text in each cell, which later need to be separated into numbers, which you don't necessarily need to do.
2) Note the lookup MATCH() functions in D23, D28, D33 used to locate each of the three inputs. Followed by some intermediate calculations.
3) Note the row and column number calculations followed by INDEX() functions in F23:J25 where the 8 required values for the trilinear interpolation are extracted from the table above.
4) Then the values are separated using LEFT() and MID() functions in the subsequent blocks, and the interpolations are computed in K30 and K34.

Study that and see what you can apply to your problem.

The eleveation and temperature are *kinda* dependent. I could always take a few sample of my own with real world elevation (same altitude) and different elevations and see what takeoff roll and other date I come up with.

From what I can see, temperature and elevation are interchangeable. If there should be more data (multiple temperatures at the same elevation or multiple elevations at the same temperature. I am not sure the data you have will tease out the separate impacts of temperature and elevation, but I could be overthinking this. (parenthetical aside: I would guess, as a non-aeronautical engineer, that the real input variable is air density that would be a function of temperature and elevation/barometric pressure. If this guess is correct, I could see replacing temperature and elevation with density as the input to your interpolation algorithm. Again, I could be overthinking this.) Can you provide a handful of worked examples? That might help us see exactly how you intend to take your four inputs and work out the ground run.

steps 2 and 3 are the lookup step of the interpolation. Because the lookup step often seems the most difficult of an interpolation algorithm, I would suggest you start there. If you understood none of this, then I would suggest you focus on the MATCH() function in D33. Can you understand how it takes the input value (Height) in D32 and returns the appropriate row number from column C?