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