Hello.
I have a table of data in an excel sheet, and have created formulas to do some linear and bi-interpolation on the data which then gives me the values I need down the line for some testing.
This works fine as a stand alone solution, but I am hoping to create a standardised sheet that can be used in multiple situations. This demands consulting multiple tables, with certain input criteria determining which table is used to interpolate from.
Below is an example table, of which there are many. Let's say I have 5 tables - named Table D25 through Table D29, and those tables each have 3 different versions based on the year chosen (2010,2016 & 2020).
IE: 2010 Table D25, 2010 Table D26 etc etc
2016 Table D25, 2016 Table D26 etc etc
2020 Table D25, 2020 Table D26 etc etc.
What I would like to do is either have a couple of drop down boxes, from which I can choose the year, and table number, and have my interpolation formulas then look at the corresponding table, or have 1 table that my interpolation formulas work from but have that table get filled out with the corresponding values based on the drop down choices.
I figure I'm in the realms of LOOKUP tables to some degree, but not sure where to go from there.
Thanks
I have a table of data in an excel sheet, and have created formulas to do some linear and bi-interpolation on the data which then gives me the values I need down the line for some testing.
This works fine as a stand alone solution, but I am hoping to create a standardised sheet that can be used in multiple situations. This demands consulting multiple tables, with certain input criteria determining which table is used to interpolate from.
Below is an example table, of which there are many. Let's say I have 5 tables - named Table D25 through Table D29, and those tables each have 3 different versions based on the year chosen (2010,2016 & 2020).
IE: 2010 Table D25, 2010 Table D26 etc etc
2016 Table D25, 2016 Table D26 etc etc
2020 Table D25, 2020 Table D26 etc etc.
What I would like to do is either have a couple of drop down boxes, from which I can choose the year, and table number, and have my interpolation formulas then look at the corresponding table, or have 1 table that my interpolation formulas work from but have that table get filled out with the corresponding values based on the drop down choices.
I figure I'm in the realms of LOOKUP tables to some degree, but not sure where to go from there.
Thanks
Auotmated Testing Analysis.xlsm | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
2 | 2010 | TABLE D25 | |||||||||||||||
3 | H70-T40 4-7kg Non-comms | APRR (Mpa/min) | Target Pressure, Ptarget (Mpa) | ||||||||||||||
4 | |||||||||||||||||
5 | Initial Tank Pressure, P0 (Mpa) | ||||||||||||||||
6 | 0.5 | 2 | 5 | 10 | 15 | 20 | 30 | 40 | 50 | 60 | 70 | >70 | |||||
7 | Ambient Temperature, Tamb (oC) | <-40 | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | ||
8 | -40 | 28.5 | 71.6 | 71.1 | 70.2 | 70.0 | 68.5 | 66.9 | 64.8 | 61.5 | 58.5 | No Fueling | No Fueling | No Fueling | |||
9 | -30 | 28.5 | 72.1 | 71.6 | 70.6 | 70.4 | 69.0 | 67.4 | 65.2 | 61.8 | 58.7 | No Fueling | No Fueling | No Fueling | |||
10 | -20 | 28.5 | 72.9 | 72.3 | 71.3 | 71.0 | 69.5 | 68.0 | 65.7 | 62.4 | 60.0 | No Fueling | No Fueling | No Fueling | |||
11 | -10 | 28.5 | 73.4 | 72.9 | 71.9 | 70.0 | 70.0 | 68.4 | 66.5 | 64.4 | 62.9 | 61.2 | No Fueling | No Fueling | |||
12 | 0 | 28.5 | 74.0 | 73.4 | 72.4 | 70.6 | 70.7 | 69.6 | 68.6 | 67.1 | 65.7 | 64.0 | No Fueling | No Fueling | |||
13 | 10 | 28.0 | 66.3 | 71.1 | 74.1 | 73.2 | 72.4 | 71.6 | 70.9 | 69.6 | 68.4 | 66.9 | No Fueling | No Fueling | |||
14 | 20 | 21.8 | 67.9 | 72.1 | 74.5 | 73.7 | 74.0 | 73.4 | 72.2 | 71.9 | 70.7 | 69.7 | No Fueling | No Fueling | |||
15 | 25 | 18.5 | 69.0 | 72.8 | 75.1 | 74.5 | 74.7 | 74.3 | 73.3 | 73.0 | 72.0 | 71.1 | No Fueling | No Fueling | |||
16 | 30 | 15.3 | 70.6 | 73.9 | 75.8 | 75.2 | 75.4 | 75.1 | 74.3 | 74.1 | 73.3 | 72.4 | 71.3 | No Fueling | |||
17 | 35 | 12.4 | 72.9 | 75.3 | 76.4 | 76.0 | 76.1 | 75.9 | 75.3 | 75.1 | 74.5 | 73.8 | 72.7 | No Fueling | |||
18 | 40 | 11.5 | 73.2 | 75.6 | 76.8 | 76.3 | 76.4 | 76.2 | 75.6 | 75.3 | 74.6 | 73.9 | 72.7 | No Fueling | |||
19 | 45 | 8.1 | 76.3 | 77.2 | 76.9 | 76.5 | 76.4 | 76.2 | 75.6 | 75.3 | 74.7 | 73.9 | 72.7 | No Fueling | |||
20 | 50 | 5.1 | 77.8 | 77.6 | 77.3 | 76.9 | 76.6 | 76.2 | 75.7 | 75.3 | 74.7 | 73.9 | 72.8 | No Fueling | |||
21 | >50 | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | No Fueling | |||
J2601 TABLES |