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

interpolation in an array
 

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

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

interpolation in an array
 

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 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

: 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

interpolation in an array
 

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

interpolation in an array
 

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


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

interpolation in an array
 

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,M ATCH(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(dat a,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

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

interpolation in an array
 

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

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

interpolation in an array
 

typo

Debug.Print ActiveCell.Formula ' 706

Debug.Print Len(ActiveCell.Formula) ' 706

can it be reduced ?

Regards,
Peter T

interpolation in an array
 

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

interpolation in an array
 

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

interpolation in an array
 

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

interpolation in an array
 

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

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

interpolation in an array
 

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

interpolation in an array
 

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
...

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

interpolation in an array
 

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