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!) :)


--
Micronaut
------------------------------------------------------------------------
Micronaut's Profile: http://www.excelforum.com/member.php...o&userid=15677
View this thread: http://www.excelforum.com/showthread...hreadid=380042

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

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

where C3=1.15


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

Thanks for the reply, much appreciated.

My numbers run in rows 2 to 151 (headings in top row). I used
=FORECAST(C2,B2:B151,A2:A151) but it isn't the expected value.

I filled column C with my mid values (1.15,1.25 etc to 15.5) and used
column D for the formula to calculate the values corresponding to the
points in column C.

I might not be understanding the FORECAST funciton correctly however.
I only need to estimate between 1.1 and 1.2, for the 1.15 value, rather
than using the entire set of data to estimate.


--
Micronaut
------------------------------------------------------------------------
Micronaut's Profile: http://www.excelforum.com/member.php...o&userid=15677
View this thread: http://www.excelforum.com/showthread...hreadid=380042

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

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" wrote in message
...
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