T
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
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