DEAR ALL

Can you pls give me how to write the INDEX AND MATCH function so that I can
get the left value in col-D interpolated for a col-D value say 18725.

col-C col-D
0.20 18800.30
0.40 18750.00
0.60 18700.80
0.80 18650.10
1.00 18620.90
1.20 17520.80

Other options to use sort col-D in ascending and then shift the col-C to
col-E will work. But pls advice only as above bcos I have hundreds of
columns like this and dont want to go around sorting etc. The data is
entered like this and want to proceed from here. Pls help this with "INDEX
AND MATCH" function or through VB or with most appropriate way.

BRGDS/CAPTGNVR

Captain,

With 18725 in cell A1, and your table in C1:D6

=TREND(OFFSET($C$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),OFFSET($D$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),A1)

This will return 0.50453648915186

HTH,
Bernie
MS Excel MVP


"CAPTGNVR" wrote in message
...
DEAR ALL

Can you pls give me how to write the INDEX AND MATCH function so that I can
get the left value in col-D interpolated for a col-D value say 18725.

col-C col-D
0.20 18800.30
0.40 18750.00
0.60 18700.80
0.80 18650.10
1.00 18620.90
1.20 17520.80

Other options to use sort col-D in ascending and then shift the col-C to
col-E will work. But pls advice only as above bcos I have hundreds of
columns like this and dont want to go around sorting etc. The data is
entered like this and want to proceed from here. Pls help this with "INDEX
AND MATCH" function or through VB or with most appropriate way.

BRGDS/CAPTGNVR

DEAR BERNIE

Thanks for the response and the formula is beyond me to understand and it is
amazing how nicely u have done it. Wish you could tell me what the formula
does bcos I have left out this 'trend' etc thinking too much for graph etc.

But the way you have cleverly mixed it up with 'offset' makes it curious for
me to know in detail.

However I tried to see for other values and the final results are not correct.
For ex when i enter 17520 in cell A1, #value error comes and when i enter
18650.1 it does not give 0.8 and instead gives .9946.

Finally if u can give me a short brief on how your formula is manipulated, i
will much obliged.

brgds/captgnvr

"Bernie Deitrick" wrote:

Captain,

With 18725 in cell A1, and your table in C1:D6

=TREND(OFFSET($C$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),OFFSET($D$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),A1)

This will return 0.50453648915186

HTH,
Bernie
MS Excel MVP


"CAPTGNVR" wrote in message
...
DEAR ALL

Can you pls give me how to write the INDEX AND MATCH function so that I can
get the left value in col-D interpolated for a col-D value say 18725.

col-C col-D
0.20 18800.30
0.40 18750.00
0.60 18700.80
0.80 18650.10
1.00 18620.90
1.20 17520.80

Other options to use sort col-D in ascending and then shift the col-C to
col-E will work. But pls advice only as above bcos I have hundreds of
columns like this and dont want to go around sorting etc. The data is
entered like this and want to proceed from here. Pls help this with "INDEX
AND MATCH" function or through VB or with most appropriate way.

BRGDS/CAPTGNVR

17520 will give you an error because you cannot interpolate with a value that is less than any given
in your table. _Extrapolation_ is different from _Interpolation_, and requires a different formula.
And an exact value doesn't require interpolation either: this version will check for the exact
match.

=IF(ISERROR(MATCH(A1,$D$1:$D$6,FALSE)),TREND(OFFSE T($C$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),OFFSET($D$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),A1),INDEX(C1:C6,MATCH(A1,$D$1:$D$6,FALSE )))

HTH,
Bernie
MS Excel MVP


"CAPTGNVR" wrote in message
...
DEAR BERNIE

Thanks for the response and the formula is beyond me to understand and it is
amazing how nicely u have done it. Wish you could tell me what the formula
does bcos I have left out this 'trend' etc thinking too much for graph etc.

But the way you have cleverly mixed it up with 'offset' makes it curious for
me to know in detail.

However I tried to see for other values and the final results are not correct.
For ex when i enter 17520 in cell A1, #value error comes and when i enter
18650.1 it does not give 0.8 and instead gives .9946.

Finally if u can give me a short brief on how your formula is manipulated, i
will much obliged.

brgds/captgnvr

"Bernie Deitrick" wrote:

Captain,

With 18725 in cell A1, and your table in C1:D6

=TREND(OFFSET($C$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),OFFSET($D$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),A1)

This will return 0.50453648915186

HTH,
Bernie
MS Excel MVP


"CAPTGNVR" wrote in message
...
DEAR ALL

Can you pls give me how to write the INDEX AND MATCH function so that I can
get the left value in col-D interpolated for a col-D value say 18725.

col-C col-D
0.20 18800.30
0.40 18750.00
0.60 18700.80
0.80 18650.10
1.00 18620.90
1.20 17520.80

Other options to use sort col-D in ascending and then shift the col-C to
col-E will work. But pls advice only as above bcos I have hundreds of
columns like this and dont want to go around sorting etc. The data is
entered like this and want to proceed from here. Pls help this with "INDEX
AND MATCH" function or through VB or with most appropriate way.

BRGDS/CAPTGNVR

DEAR BERNIE
Thnks for the quick folo up. For your info, the cells in col-C is the
sounding of tanks and to the right are the quantities.

So sometimes it is needed to put the quantity and get the interpolated
soundings.

So pls suggest best means.

br/captgnvr

"Bernie Deitrick" wrote:

17520 will give you an error because you cannot interpolate with a value that is less than any given
in your table. _Extrapolation_ is different from _Interpolation_, and requires a different formula.
And an exact value doesn't require interpolation either: this version will check for the exact
match.

=IF(ISERROR(MATCH(A1,$D$1:$D$6,FALSE)),TREND(OFFSE T($C$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),OFFSET($D$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),A1),INDEX(C1:C6,MATCH(A1,$D$1:$D$6,FALSE )))

HTH,
Bernie
MS Excel MVP


"CAPTGNVR" wrote in message
...
DEAR BERNIE

Thanks for the response and the formula is beyond me to understand and it is
amazing how nicely u have done it. Wish you could tell me what the formula
does bcos I have left out this 'trend' etc thinking too much for graph etc.

But the way you have cleverly mixed it up with 'offset' makes it curious for
me to know in detail.

However I tried to see for other values and the final results are not correct.
For ex when i enter 17520 in cell A1, #value error comes and when i enter
18650.1 it does not give 0.8 and instead gives .9946.

Finally if u can give me a short brief on how your formula is manipulated, i
will much obliged.

brgds/captgnvr

"Bernie Deitrick" wrote:

Captain,

With 18725 in cell A1, and your table in C1:D6

=TREND(OFFSET($C$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),OFFSET($D$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),A1)

This will return 0.50453648915186

HTH,
Bernie
MS Excel MVP


"CAPTGNVR" wrote in message
...
DEAR ALL

Can you pls give me how to write the INDEX AND MATCH function so that I can
get the left value in col-D interpolated for a col-D value say 18725.

col-C col-D
0.20 18800.30
0.40 18750.00
0.60 18700.80
0.80 18650.10
1.00 18620.90
1.20 17520.80

Other options to use sort col-D in ascending and then shift the col-C to
col-E will work. But pls advice only as above bcos I have hundreds of
columns like this and dont want to go around sorting etc. The data is
entered like this and want to proceed from here. Pls help this with "INDEX
AND MATCH" function or through VB or with most appropriate way.

BRGDS/CAPTGNVR

So sometimes it is needed to put the quantity and get the interpolated
soundings.

So pls suggest best means.

The formula that I just posted, perhaps?

HTH,
Bernie
MS Excel MVP


=IF(ISERROR(MATCH(A1,$D$1:$D$6,FALSE)),TREND(OFFSE T($C$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),OFFSET($D$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),A1),INDEX(C1:C6,MATCH(A1,$D$1:$D$6,FALSE )))

You did not give your definition of "interpolate" or examples of desired
answers for several numbers. This may??? be what you want for col A when
your number is entered in c1

=INDEX(A:A,MATCH(C1,B:B,-1))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CAPTGNVR" wrote in message
...
DEAR BERNIE

Thanks for the response and the formula is beyond me to understand and it
is
amazing how nicely u have done it. Wish you could tell me what the
formula
does bcos I have left out this 'trend' etc thinking too much for graph
etc.

But the way you have cleverly mixed it up with 'offset' makes it curious
for
me to know in detail.

However I tried to see for other values and the final results are not
correct.
For ex when i enter 17520 in cell A1, #value error comes and when i enter
18650.1 it does not give 0.8 and instead gives .9946.

Finally if u can give me a short brief on how your formula is manipulated,
i
will much obliged.

brgds/captgnvr

"Bernie Deitrick" wrote:

Captain,

With 18725 in cell A1, and your table in C1:D6

=TREND(OFFSET($C$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),OFFSET($D$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),A1)

This will return 0.50453648915186

HTH,
Bernie
MS Excel MVP


"CAPTGNVR" wrote in message
...
DEAR ALL

Can you pls give me how to write the INDEX AND MATCH function so that I
can
get the left value in col-D interpolated for a col-D value say 18725.

col-C col-D
0.20 18800.30
0.40 18750.00
0.60 18700.80
0.80 18650.10
1.00 18620.90
1.20 17520.80

Other options to use sort col-D in ascending and then shift the col-C
to
col-E will work. But pls advice only as above bcos I have hundreds of
columns like this and dont want to go around sorting etc. The data is
entered like this and want to proceed from here. Pls help this with
"INDEX
AND MATCH" function or through VB or with most appropriate way.

BRGDS/CAPTGNVR

DEAR DON

Thanks and tried yours but it does not interpolate. The interpolation i
need is asf:

For ex: if quantity is 18640 tons in cell a1, it should interpolate between
0.80 and 1.00 and give the result.

Pls suggest.

br/captgnvr

"Don Guillett" wrote:

You did not give your definition of "interpolate" or examples of desired
answers for several numbers. This may??? be what you want for col A when
your number is entered in c1

=INDEX(A:A,MATCH(C1,B:B,-1))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CAPTGNVR" wrote in message
...
DEAR BERNIE

Thanks for the response and the formula is beyond me to understand and it
is
amazing how nicely u have done it. Wish you could tell me what the
formula
does bcos I have left out this 'trend' etc thinking too much for graph
etc.

But the way you have cleverly mixed it up with 'offset' makes it curious
for
me to know in detail.

However I tried to see for other values and the final results are not
correct.
For ex when i enter 17520 in cell A1, #value error comes and when i enter
18650.1 it does not give 0.8 and instead gives .9946.

Finally if u can give me a short brief on how your formula is manipulated,
i
will much obliged.

brgds/captgnvr

"Bernie Deitrick" wrote:

Captain,

With 18725 in cell A1, and your table in C1:D6

=TREND(OFFSET($C$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),OFFSET($D$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),A1)

This will return 0.50453648915186

HTH,
Bernie
MS Excel MVP


"CAPTGNVR" wrote in message
...
DEAR ALL

Can you pls give me how to write the INDEX AND MATCH function so that I
can
get the left value in col-D interpolated for a col-D value say 18725.

col-C col-D
0.20 18800.30
0.40 18750.00
0.60 18700.80
0.80 18650.10
1.00 18620.90
1.20 17520.80

Other options to use sort col-D in ascending and then shift the col-C
to
col-E will work. But pls advice only as above bcos I have hundreds of
columns like this and dont want to go around sorting etc. The data is
entered like this and want to proceed from here. Pls help this with
"INDEX
AND MATCH" function or through VB or with most appropriate way.

BRGDS/CAPTGNVR