interpolation in an array

[[email protected]]

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

[vandenberg p]

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

[vandenberg p]

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

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

[keepITcool]

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

[keepITcool]

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

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

[Peter T]

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
>

[Peter T]

typo

> Debug.Print ActiveCell.Formula ' 706

Debug.Print Len(ActiveCell.Formula) ' 706

can it be reduced ?

Regards,
Peter T