interpolation in an array

Hi
I have an array in which i need to interpolate(linear).

For ex,

5 6 7 8
2 60 70 80 90
3 50 60 70 80
4 40 50 60 70
5 30 40 50 60

The first row (Xvalues) and the first column (Yvalues):
For ex, for X=5.5 and Y=2.5, my interpolated values should be 60. How
can i write a formula for this? I would greatly appreciate any help in
this.
Thanks so much
GP

If I understand your problem and assuming that the difference
in each value is always 10 then the following should work.

Assume you have the data table starting in A1 with a blank
and A2 has the value 2, B1 has the value 5 etc.
Then B6 has the x value; B7 has the Y value and B8
has the formula as shown below.


5 6 7 8
2 60 70 80 90
3 50 60 70 80
4 40 50 60 70
5 30 40 50 60
x 5.5 <----Your X goes here
y 2.5 <----Your Y goes here
Value 60 =HLOOKUP(INT(B6),A1:E5,INT(B7))+B27*10-(MOD(B7,INT(B7))*10)

Pieter Vandenberg

[email protected] wrote:
: Hi
: I have an array in which i need to interpolate(linear).

: For ex,

: 5 6 7 8
: 2 60 70 80 90
: 3 50 60 70 80
: 4 40 50 60 70
: 5 30 40 50 60

: The first row (Xvalues) and the first column (Yvalues):
: For ex, for X=5.5 and Y=2.5, my interpolated values should be 60. How
: can i write a formula for this? I would greatly appreciate any help in
: this.
: Thanks so much
: GP

Sorry, I forgot to clean up one part of the formula.
Here is the corrected version:

=HLOOKUP(INT(B6),A1:E5,INT(B7))+MOD(B6,INT(B6))*10-(MOD(B7,INT(B7))*10)
(Note that the reference to B27 has been replaced)

Pieter Vandenberg

vandenberg p <[email protected]> wrote:

: If I understand your problem and assuming that the difference
: in each value is always 10 then the following should work.

: Assume you have the data table starting in A1 with a blank
: and A2 has the value 2, B1 has the value 5 etc.
: Then B6 has the x value; B7 has the Y value and B8
: has the formula as shown below.


: 5 6 7 8
: 2 60 70 80 90
: 3 50 60 70 80
: 4 40 50 60 70
: 5 30 40 50 60
: x 5.5 <----Your X goes here
: y 2.5 <----Your Y goes here
: Value 60 =HLOOKUP(INT(B6),A1:E5,INT(B7))+B27*10-(MOD(B7,INT(B7))*10)

: Pieter Vandenberg

: [email protected] wrote:
: : Hi
: : I have an array in which i need to interpolate(linear).

: : For ex,

: : 5 6 7 8
: : 2 60 70 80 90
: : 3 50 60 70 80
: : 4 40 50 60 70
: : 5 30 40 50 60

: : The first row (Xvalues) and the first column (Yvalues):
: : For ex, for X=5.5 and Y=2.5, my interpolated values should be 60. How
: : can i write a formula for this? I would greatly appreciate any help in
: : this.
: : Thanks so much
: : GP

Thank you verymuch pieter. I appreciate it. Now if the difference
between each value is in decimals (Not 10), then how will we
interpolate?. I mean if the array looks like,
5 6 7 8
2 62.6 72.4 81 92
3 51.2 60 70 80
4 40 50 60 70
5 30 40 50 60

I would appreciate the help.
Thanks a lot
GP

1.

this is a question for worksheetfunction newsgroup
as it has nothing to do with (VBA) programming..

2.
basically it's VERY simple.
the match and the (H/V)looKup functions will interpolate
unLESS told not to.

5 6 7 8
2 62.6 72.4 81 92
3 51.2 60 70 80
4 40 50 60 70
5 30 40 50 60

2.5
5.5 62.6
=INDEX(B2:E5,MATCH(A7,A2:A5),MATCH(A8,B1:E1))


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


[email protected] wrote :

> 5 6 7 8
> 2 62.6 72.4 81 92
> 3 51.2 60 70 80
> 4 40 50 60 70
> 5 30 40 50 60

Try this for size

Define named ranges
xx = Scale X labels
yy = Scale y labels
data = table of values
xi = Input X
yi = Input Y

=((yi-INDEX(yy,MATCH(yi,yy)))/(INDEX(yy,MATCH(yi,yy)+1)-INDEX(yy,MATCH(yi,yy
))))*((((xi-INDEX(xx,MATCH(xi,xx)))/(INDEX(xx,MATCH(xi,xx)+1)-INDEX(xx,MATCH
(xi,xx))))*(INDEX(data,MATCH(yi,yy)+1,MATCH(xi,xx)+1,1)-INDEX(data,MATCH(yi,
yy)+1,MATCH(xi,xx),1))+INDEX(data,MATCH(yi,yy)+1,MATCH(xi,xx),1))-(((xi-INDE
X(xx,MATCH(xi,xx)))/(INDEX(xx,MATCH(xi,xx)+1)-INDEX(xx,MATCH(xi,xx))))*(INDE
X(data,MATCH(yi,yy),MATCH(xi,xx)+1,1)-INDEX(data,MATCH(yi,yy),MATCH(xi,xx),1
))+INDEX(data,MATCH(yi,yy),MATCH(xi,xx),1)))+(((xi-INDEX(xx,MATCH(xi,xx)))/(
INDEX(xx,MATCH(xi,xx)+1)-INDEX(xx,MATCH(xi,xx))))*(INDEX(data,MATCH(yi,yy),M
ATCH(xi,xx)+1,1)-INDEX(data,MATCH(yi,yy),MATCH(xi,xx),1))+INDEX(data,MATCH(y
i,yy),MATCH(xi,xx),1))

Debug.Print ActiveCell.Formula ' 706

5 6 7 8 (xx)
2 62.6 72.4 81 92
3 51.2 60 70 80
4 40 50 60 70
5 30 40 50 60 (data)
(yy)

xi = 5.5
yi = 2.5
result = 61.55

xi >= min(xx) and xi <max(xx)
So in the eg change 8 to 8.00000001
and similar re yi / yy

Regards,
Peter T

<[email protected]> wrote in message
news:[email protected]...
> Thank you verymuch pieter. I appreciate it. Now if the difference
> between each value is in decimals (Not 10), then how will we
> interpolate?. I mean if the array looks like,
> 5 6 7 8
> 2 62.6 72.4 81 92
> 3 51.2 60 70 80
> 4 40 50 60 70
> 5 30 40 50 60
>
> I would appreciate the help.
> Thanks a lot
> GP
>

rereading the other replies i may have misunderstood
the original question. <g>

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

typo

> Debug.Print ActiveCell.Formula ' 706

Debug.Print Len(ActiveCell.Formula) ' 706

can it be reduced ?

Regards,
Peter T

hmm

using trend you'd need some xtra cells.
ofcourse the sequence(row/col or col/row
is important in interpolation.

5 6 7 8
2 62.6 72.4 81 92
3 51.2 60 70 80
4 40 50 60 70
5 30 40 50 60

2.5 56.85 66.32 75.55 86.1
5.5 61.507

Formula in row7 =TREND(B2:B5,$A$2:$A$5,$A$7)
Formula in row8 =TREND(B7:E7,$B$1:$E$1,$A$8)

--






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Peter T wrote :

> typo
>
> > Debug.Print ActiveCell.Formula ' 706
>
> Debug.Print Len(ActiveCell.Formula) ' 706
>
> can it be reduced ?
>
> Regards,
> Peter T

Hi stranger, good to see you back !

I think this trend method will only linearly interpolate if the data
intervals are also linear in both directions.

With the sample data and inputs 2.5 & 5.5 the result should be 61.55 (not
61.507)

With inputs 4 & 7 should return the intersect 60 (trend formulas returned
60.208)

FWIW, I wouldn't want to use the long formula I posted. Instead a bunch of
simpler smaller formulas (from which that long one was derived) or a UDF.

Regards,
Peter T

"keepITcool" <[email protected]> wrote in message
news:[email protected]...
> hmm
>
> using trend you'd need some xtra cells.
> ofcourse the sequence(row/col or col/row
> is important in interpolation.
>
> 5 6 7 8
> 2 62.6 72.4 81 92
> 3 51.2 60 70 80
> 4 40 50 60 70
> 5 30 40 50 60
>
> 2.5 56.85 66.32 75.55 86.1
> 5.5 61.507
>
> Formula in row7 =TREND(B2:B5,$A$2:$A$5,$A$7)
> Formula in row8 =TREND(B7:E7,$B$1:$E$1,$A$8)
>
> --
>
>
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Peter T wrote :
>
> > typo
> >
> > > Debug.Print ActiveCell.Formula ' 706
> >
> > Debug.Print Len(ActiveCell.Formula) ' 706
> >
> > can it be reduced ?
> >
> > Regards,
> > Peter T

Thanks a lot. wow, it's realy amazing that there are people out there
with so much helping tendency. I really appreciate it. As peter told,
the long formula works well for the values with in the range. But the
following gives a little bit touble. Is there anyway to put this long
formula with IF conditions so that this will use HLOOKUP for the values
present and interpolate for the values not present. i mean i would not
want to change the values from 8 to 8.00000001 in the example.
xi >= min(xx) and xi <max(xx)
So in the eg change 8 to 8.00000001
and similar re yi / yy

Thanks again
Regards
GP

> i mean i would not
> want to change the values from 8 to 8.00000001 in the example.

Why is it a problem to add say 1*e-12 to only the last X & Y label/values.

Or are you saying you want to interpolate outside the range of data, If so
that's an entirely different matter, particularly if your data intervals are
non-linear, in either or both horizontal or vertical directions. That's
extrapolation and the method and formulas you would deploy would depend on
the type of data you have, how far you want to extend and probably other
factors.

Here's the origin of that big formula, paste following into H1:H13

5.5
2.5
=INDEX(xx,MATCH(H1,xx))
=INDEX(xx,MATCH(H1,xx)+1)
=INDEX(yy,MATCH(H2,yy))
=INDEX(yy,MATCH(H2,yy)+1)
=INDEX(data,MATCH(H2,yy),MATCH(H1,xx),1)
=INDEX(data,MATCH(H2,yy)+1,MATCH(H1,xx),1)
=INDEX(data,MATCH(H2,yy),MATCH(H1,xx)+1,1)
=INDEX(data,MATCH(H2,yy)+1,MATCH(H1,xx)+1,1)
=((H1-H3)/(H4-H3))*(H9-H7)+H7
=((H1-H3)/(H4-H3))*(H10-H8)+H8
=((H2-H5)/(H6-H5))*(H12-H11)+H11

Assumes named ranges xx, yy & data as I described before. You could also
name H1 as xi & H2 as yi (the inputs) so you can also use the big formula.

You could extend the last formula (in H13) to include an IF condition, or
maybe adapt some of the earlier formulas to your requirements.

When done you could assemble it all into your own jumbo formula, but ensure
it does not exceed the max formula length of 1024.

Regards,
Peter T

<[email protected]> wrote in message
news:[email protected]...
> Thanks a lot. wow, it's realy amazing that there are people out there
> with so much helping tendency. I really appreciate it. As peter told,
> the long formula works well for the values with in the range. But the
> following gives a little bit touble. Is there anyway to put this long
> formula with IF conditions so that this will use HLOOKUP for the values
> present and interpolate for the values not present. i mean i would not
> want to change the values from 8 to 8.00000001 in the example.
> xi >= min(xx) and xi <max(xx)
> So in the eg change 8 to 8.00000001
> and similar re yi / yy
>
> Thanks again
> Regards
> GP
>

> > i mean i would not
> > want to change the values from 8 to 8.00000001 in the example.
>

If you really don't want to change the 8, include an additional step for
your inputs (following in named cell xi or H1 in the previous example).

=IF(Xinput= 8,Xinput-18e-12,Xinput)
or
=IF(Xinput= MAX(xx),Xinput-0.000000000018,Xinput)

similar for the Yinput

Regards,
Peter T

not sure how "18" crept in

> =IF(Xinput= 8,Xinput-(1e-12),Xinput)

ie ensure the input is a tad less than 8 or less than the maximum xValue

Peter T

"Peter T" <peter_t@discussions> wrote in message
news:#[email protected]...
>
> > > i mean i would not
> > > want to change the values from 8 to 8.00000001 in the example.
> >
>
> If you really don't want to change the 8, include an additional step for
> your inputs (following in named cell xi or H1 in the previous example).
>
> =IF(Xinput= 8,Xinput-18e-12,Xinput)
> or
> =IF(Xinput= MAX(xx),Xinput-0.000000000018,Xinput)
>
> similar for the Yinput
>
> Regards,
> Peter T
>
>
>

Wow, fantastic peter. I pledge that i am not a programmer and i am
learning it myself (Self study!). You really helped me in understanding
this interpolation. Thanks a lot to all
Regards
GP