Looking up values in a column of unordered values by interpolation

[timothy_johnsen]

Hello,
I have a challenging problem to solve in excel. Below is the basic
data with row numbers and column letters. Cells are separated by
spaces. Note that the real data are 100's of rows long.

A B C
1 0 3 1.2
2 1000 5 2.1
3 2000 2 5.9
4 3000 1 4.0
5 4000 6 6.5
6 5000 4 3.4

Features of the data: 1) Columns B and C are amounts (concentrations
of a chemical) that are individually related to column A that is the
age, 2) Column A is in ascending order, 3) Columns B and C are
unordered, and there is no exact match between these columns, 4) The
values in column B are decimal values in reality but I just use whole
numbers in the example for simplicity.

What the function solution should do:
Basically, the function should use values in C to look up the values
in B and return the corresponding age from A. In other words, it
answers the question, "At what point in time is the concentration of B
the same as in C?" As there is not an exact match in B for values in
C, the function must use the value from C to interpolate the position
of the value in B and the corresponding interpolated age in A. Note
that the answers should be: 2800, 1966, 4050, 5000, 5250, and 200
years - 5250 is extrapolated as C (6.5) is higher than any B. Also,
note that the B and C data have trends that ascend and descend over
time. This means that there are ranges of data that overlap. That is,
theoretically there could be more than one correct answer for a given
C. This is OK, as long as excel produces one of the correct answers.
Later I can visually check the graph to see if the answer falls within
or is close to zones of overlapping values.

The Match and Lookup functions require data to be ordered when there
is not an exact match. I cannot reorder the data. Only column A has
ordered data.

I really appreciate any help with this excel puzzle. I have lost
too many brain cells attempting it on my own.
Good luck!
Fred

 

[Thomas Hardy]

Hello,
I have a challenging problem to solve in excel. Below is the basic
data with row numbers and column letters. Cells are separated by
spaces. Note that the real data are 100's of rows long.

A B C
1 0 3 1.2
2 1000 5 2.1
3 2000 2 5.9
4 3000 1 4.0
5 4000 6 6.5
6 5000 4 3.4

Features of the data: 1) Columns B and C are amounts (concentrations
of a chemical) that are individually related to column A that is the
age, 2) Column A is in ascending order, 3) Columns B and C are
unordered, and there is no exact match between these columns, 4) The
values in column B are decimal values in reality but I just use whole
numbers in the example for simplicity.

What the function solution should do:
Basically, the function should use values in C to look up the values
in B and return the corresponding age from A. In other words, it
answers the question, "At what point in time is the concentration of B
the same as in C?" As there is not an exact match in B for values in
C, the function must use the value from C to interpolate the position
of the value in B and the corresponding interpolated age in A. Note
that the answers should be: 2800, 1966, 4050, 5000, 5250, and 200
years - 5250 is extrapolated as C (6.5) is higher than any B. Also,
note that the B and C data have trends that ascend and descend over
time. This means that there are ranges of data that overlap. That is,
theoretically there could be more than one correct answer for a given
C. This is OK, as long as excel produces one of the correct answers.
Later I can visually check the graph to see if the answer falls within
or is close to zones of overlapping values.

The Match and Lookup functions require data to be ordered when there
is not an exact match. I cannot reorder the data. Only column A has
ordered data.

I really appreciate any help with this excel puzzle. I have lost
too many brain cells attempting it on my own.
Good luck!
Fred

Fred,

You could make a copy of the values in columns B and A (ordered by B) and do
a VLOOKUP with range_lookup set to True.

To keep your data together, you could make the copies of B and A in a
separate sheet, and to make the process easier you could record a simple
macro to copy B and A for you automatically.

=VLOOKUP(C1,Sheet2!A:B,2,TRUE)

The macro would look something like this; the VLookups would update
automatically.

Sheets("Sheet1").Select
Columns("A:A").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:B").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Maybe there are easier ways...

Thomas

 

[KC Rippstein]

You could set up column E as a sorted list of column B. E1=SMALL(B:B,ROW())
and use the fill handle to copy down as far as you need.
Then in D1, use this formula:
=OFFSET(INDEX(B:B,MATCH(MATCH(C1,E:E),B:B,0)),0,-1)
and use the fill handle to drag down as far as you need.
This matches C1 with the closest equivalent in sorted column E that does not
go over the value in C1, then finds that exact match in column B and returns
the value next to it in column A.
- KC

 

[Bernd]

Hi Fred,

This is kind of tricky, I think.

My suggested formula is
=IF(CEILING(C1,1)=FLOOR(C1,1),INDEX($A$1:$A$7,MATCH(FLOOR(C1,1),$B$1:$B
$7,)),INDEX($A$1:$A$7,MATCH(FLOOR(C1,1),$B$1:$B$7,))+(C1-
FLOOR(C1,1))*(INDEX($A$1:$A$7,MATCH(CEILING(C1,1),$B$1:$B$7,))-INDEX($A
$1:$A$7,MATCH(FLOOR(C1,1),$B$1:$B$7,)))/(CEILING(C1,1)-FLOOR(C1,1)))

Please note that this formula only "inter"polates. It does not
"extra"polate: Your value 6.5 will result in an error. But you can
easily fix upcoming errors by having a look at your data and by
enhancing it: Insert another row (always at the end so that additional
actual/real data will be inserted before that and taken by the formula
later!) with x value 7. For 6 your data shows 4000, for 5 we have
1000, so 7 should have 4000 + (4000 - 1000) = 7000.

Regards,
Bernd