extrapolate data from series

Hi,

Back again! I have a column of data in A running from 1.1 to 16 in 0.1 increments (1.1, 1.2, 1.3, all the way to 16). Next to this column I have a column of data corresponding to each of these points.

I am trying to figure out how to extrapolate data from this series in B. For example, I have the value for 1.1, 1.2, 1.3 etc, but I want to work out the value at 1.15, or 1.35. Is it possible to set up a cell where I could put in my point from column A, e.g 1.75 and have it work out what the point would be from the data in column B?

Thanks for any help with this, it is basic math, I'm sure, but my values in column B are standard index form to 10 decimal places (e.g 1.2207E-10), so it becomes a bit of head wrecker (for me anyway!)

Did you look at the forecats and the trend worksheet functions.

=FORECAST(C3,B1:B11,A1:A11)

where C3=1.15


Mangesh

You are probably looking for this (interpolation) formula:

=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)

Substitute A12 or B12 with your last row 151.

Mangesh

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

To get the values above and below I suggest the following:

Below
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{0,10,20,30,40})}

This will return say 0 if a1 is between 0-9 , 10 if it is between 10-
19 etc etc

Above
{=3DSUM(--(A1>{0,9,19,29})*--(A1<{10,20,30,40})*{10,20,30,40,50})}

The result of these formulas would be the lower and upper nodes in your
interpolation formula.


The linear interp function for the case above would be something like
this:

interp value =3D X0+ (X0 - X1)/(1.2-1.1) * (1.15-1.1)

where X is the data column.


They are equivalent to a series of nested IF=B4s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

Hi Ben,

try something like this:

Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:

=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))


Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)

2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.

Mangesh



"Ben" <Ben@discussions.microsoft.com> wrote in message
news:5754DBE4-209D-4394-94FF-4B6D72F18920@microsoft.com...
> Mangesh,
>
> I have come across your response to this thread and you're obviously the
> person I need to communicate with. I have tried to use your formula but
as
> yet have got nowhere.
>
> I have a similar query, I need to interpolate for a value in a table in
this
> case tank volumes and other info.
>
> Depth of water is in 20 cm increments in col A with volume and other info
> in cols B through E I would like to extract info from any given 1 cm
> increment .
>
> Alternatively I can do the maths if someone can tell me how to get the
cell
> above and below my required value in col A for vlookup.
>
> Many thanks
>
> Ben
>
> "mangesh_yadav" wrote:
>
> >
> > You are probably looking for this (interpolation) formula:
> >
> >
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
> >
> > Substitute A12 or B12 with your last row 151.
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=380042
> >
> >

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>

Mangesh,

I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but as
yet have got nowhere.

I have a similar query, I need to interpolate for a value in a table in this
case tank volumes and other info.

Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .

Alternatively I can do the maths if someone can tell me how to get the cell
above and below my required value in col A for vlookup.

Many thanks

Ben

"mangesh_yadav" wrote:

>
> You are probably looking for this (interpolation) formula:
>
> =(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LOOKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,2)-LOOKUP(C2,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2:$B$12)
>
> Substitute A12 or B12 with your last row 151.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=380042
>
>