interpolation in an array

  • Thread starter Thread starter gnanapragasamv
  • Start date Start date
G

gnanapragasamv

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

(e-mail address removed) 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


: 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

: (e-mail address removed) 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))
 
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
 
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 :
 
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
 
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.
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

interpolation 3
Linear Interpolation 16
interpolating a number 5
Monthly Report 5
Lookup Table 1
How to interpolate in an array forX and Y values to get Z? 2
Changing values in a table 2
Arranging data 3

Back
Top