Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
interpolation in an array
rereading the other replies i may have misunderstood
the original question. <g -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
interpolation in an array
typo
Debug.Print ActiveCell.Formula ' 706 Debug.Print Len(ActiveCell.Formula) ' 706 can it be reduced ? Regards, Peter T |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
interpolation | Excel Worksheet Functions | |||
interpolation | New Users to Excel | |||
Interpolation | New Users to Excel | |||
Interpolation | Excel Worksheet Functions | |||
help with interpolation and limit of interpolation | Excel Discussion (Misc queries) |