Interpolation/vlookup question.

[yuany77]

Basically i got 4 tables.
table 1: temperature for different elevation for part 1 and 2.
table 2: concentration for different elevation for part 1 and 2.
table 4: Cumulative population for different elevation for part 1 and
2.

Table 3 are what I want to calculate. I want to get the elevation
range(low limit and high limit) which satisfy two criteria:
temperature <10 and concentration >5 from table 1 and 2. And then
calculate the population in these range using table 4.

Anyone can help?

Thanks a lot.

stone.





1 Temperature
Elevation Part 1 Part 2
1355.5 10.42 10.87
1354.5 10.06 10.39
1353.5 9.78 9.98
1352.5 9.62 9.7
1351.5 9.57 9.5
1350.5 9.52 9.43

2 Concentration
Elevation Part 1 Part 2
1355.50 7.50 8.20
1354.50 7.00 8.00
1353.50 6.50 7.70
1352.50 6.00 7.30
1351.50 5.50 6.80
1350.50 4.20 5.20

3
Elevation Part 1 Part 2
Low limit
High limit
population


4 Cumulative population
Elevation Part 1 Part 2
1356.3 1,921 16,772
1355 886 9,491
1354.5 355 4,158
1353.2 76 1,246
1352.6 0 289
1351.8 0 118
1349.3 0 26

 

[Bob Bridges]

Yeah, I think so, yuany77. Let me describe what I think would work, and you
tell me if I'm on the right track:

1) Search table 1 for the first row where the temperature falls below 10°,
and mark that elevation. Any elevation from there up is your first
acceptable range....No, wait - you have the temperature RISING as the
elevation goes up. Can that be right? Ok, then any elevation from there
down is your first range.

2) Search table 2 for the last elevation where the concentration is above 5.
That's the lowest acceptable elevation for the 2nd criterion; your range is
now between the previous elevation and this one is your range. (If elevation
1 is below elevation 2, you have an error; no elevation matches your
requirements.)

3) The desired rows in table 4 are those whose elevations fall between the
two limits found above. Add up the populations in those rows.

Is that what you need? Looks to me like you simply need a MATCH for the
first two, and a SUM(INDIRECT()) for the third step. Or am I missing
something?

 

[Lori]

Using linear interpolation and assuming population is the difference between
the two observations:

Lo 1351.1 1350.4*
Hi 1354.3 1353.5
Pop 309.0 1961.7

Hi =percentile(Elevs,percentrank(Temps,10,30)) etc.

(*This observation needs to be extrapolated.)