extrapolate data from series

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Micronaut - 17 Jun 2005 12:10 GMT

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

Signature

Micronaut

Reply to this Message

mangesh_yadav - 17 Jun 2005 12:23 GMT

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

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

where C3=1.15

Mangesh

Signature

mangesh_yadav

Reply to this Message

Micronaut - 17 Jun 2005 13:45 GMT

Thanks for the reply, much appreciated.

My numbers run in rows 2 to 151 (headings in top row). I use
=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 use
column D for the formula to calculate the values corresponding to th
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, rathe
than using the entire set of data to estimate

Reply to this Message

mangesh_yadav - 20 Jun 2005 06:01 GMT

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

Signature

mangesh_yadav

Reply to this Message

Ben - 06 Jul 2005 11:01 GMT

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

> You are probably looking for this (interpolation) formula:
>
[quoted text clipped - 3 lines]
>
> Mangesh

Reply to this Message

sebas_cp@hotmail.com - 06 Jul 2005 13:16 GMT

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

Below
{=SUM(--(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
{=SUM(--(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 = 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´s with ANDS included,
albeit more clean and readble IMHO.

Hope this helps

Reply to this Message

Mangesh Yadav - 07 Jul 2005 06:12 GMT

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

> Mangesh,
>
[quoted text clipped - 17 lines]
>
> > 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.
> >
[quoted text clipped - 5 lines]
> > mangesh_yadav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10470
> > View this thread: http://www.excelforum.com/showthread.php?threadid=380042

Reply to this Message