| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
interesting interpolation
G'Day guys, need some help with this code. am trying to do a graphica interpolation by VB. I have attached a simple example of what I need. The values column nee to be filled! Any idea? So far I can get the initial cells. For example for the firs two values I can get out 2 and 3 in the A axis and 4 and 5 in the axis. But keep getting errors everytime I try and get the four cell which the above 4 values intersect at (as soon as I can get thos intersecting cells it is simple maths, no problems). Thanks in advance for any ideas +------------------------------------------------------------------- |Filename: Interpolation example.doc |Download: http://www.excelforum.com/attachment.php?postid=3875 +------------------------------------------------------------------- -- nel ----------------------------------------------------------------------- nelg's Profile: http://www.excelforum.com/member.php...fo&userid=2777 View this thread: http://www.excelforum.com/showthread.php?threadid=47284 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
interesting interpolation
Your attachment didn't make it through - good thing - attachments are tabu.
Repost and put your stuff into the post (not as an attachment)... -- steveB Remove "AYN" from email to respond "nelg" wrote in message ... G'Day guys, need some help with this code. am trying to do a graphical interpolation by VB. I have attached a simple example of what I need. The values column need to be filled! Any idea? So far I can get the initial cells. For example for the first two values I can get out 2 and 3 in the A axis and 4 and 5 in the B axis. But keep getting errors everytime I try and get the four cells which the above 4 values intersect at (as soon as I can get those intersecting cells it is simple maths, no problems). Thanks in advance for any ideas. +-------------------------------------------------------------------+ |Filename: Interpolation example.doc | |Download: http://www.excelforum.com/attachment.php?postid=3875 | +-------------------------------------------------------------------+ -- nelg ------------------------------------------------------------------------ nelg's Profile: http://www.excelforum.com/member.php...o&userid=27771 View this thread: http://www.excelforum.com/showthread...hreadid=472841 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
interesting interpolation
He is posting in the Excel forum where the attachment can be accessed. A
link is certainly OK in the newsgroup, but these links don't work outside the Excel forum. -- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:QFk0f.127$%W3.40@trnddc08... Your attachment didn't make it through - good thing - attachments are tabu. Repost and put your stuff into the post (not as an attachment)... -- steveB Remove "AYN" from email to respond "nelg" wrote in message ... G'Day guys, need some help with this code. am trying to do a graphical interpolation by VB. I have attached a simple example of what I need. The values column need to be filled! Any idea? So far I can get the initial cells. For example for the first two values I can get out 2 and 3 in the A axis and 4 and 5 in the B axis. But keep getting errors everytime I try and get the four cells which the above 4 values intersect at (as soon as I can get those intersecting cells it is simple maths, no problems). Thanks in advance for any ideas. +-------------------------------------------------------------------+ |Filename: Interpolation example.doc | |Download: http://www.excelforum.com/attachment.php?postid=3875 | +-------------------------------------------------------------------+ -- nelg ------------------------------------------------------------------------ nelg's Profile: http://www.excelforum.com/member.php...o&userid=27771 View this thread: http://www.excelforum.com/showthread...hreadid=472841 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
interesting interpolation
Tom,
My bad - I overlooked the link at the end of the post. Time for me to investigate this formum... (looks interesting)... -- steveB Remove "AYN" from email to respond "Tom Ogilvy" wrote in message ... He is posting in the Excel forum where the attachment can be accessed. A link is certainly OK in the newsgroup, but these links don't work outside the Excel forum. -- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:QFk0f.127$%W3.40@trnddc08... Your attachment didn't make it through - good thing - attachments are tabu. Repost and put your stuff into the post (not as an attachment)... -- steveB Remove "AYN" from email to respond "nelg" wrote in message ... G'Day guys, need some help with this code. am trying to do a graphical interpolation by VB. I have attached a simple example of what I need. The values column need to be filled! Any idea? So far I can get the initial cells. For example for the first two values I can get out 2 and 3 in the A axis and 4 and 5 in the B axis. But keep getting errors everytime I try and get the four cells which the above 4 values intersect at (as soon as I can get those intersecting cells it is simple maths, no problems). Thanks in advance for any ideas. +-------------------------------------------------------------------+ |Filename: Interpolation example.doc | |Download: http://www.excelforum.com/attachment.php?postid=3875 | +-------------------------------------------------------------------+ -- nelg ------------------------------------------------------------------------ nelg's Profile: http://www.excelforum.com/member.php...o&userid=27771 View this thread: http://www.excelforum.com/showthread...hreadid=472841 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
interesting interpolation
HEre's a relatively simple solution that can be made to work on you sample data set. 1) Make the column of A values the leftmost column in the lookup tabl rather than the rightmost column. I'm going to assume lookup table i in the range A3:E7 2) Create a new table that looks like this starting in A11 (newA an newB can be in any cell): [blank] lowB highB A\B =INT(newB) =INT(newB+1) =INT(newA) =VLOOKUP($A13,$A$3:$E$7,B$12) =VLOOKUP($A13,$A$3:$E$7,B$12) =INT(newA+1) =VLOOKUP($A13,$A$3:$E$7,B$12) =VLOOKUP($A13,$A$3:$E$7,B$12) That will extract the desired boundary points for the range over whic you wish to interpolate. Then, as you say, the interpolation itself i simple. Important note: This method only works if the defining A and B serie in the lookup table are integer series {1,2,3,4,5,...}. It would b readily adapted to cases where the series has an easily defined patter (say {0.5,1,1.5,2,2.5,...}). If there isn't a simple pattern to A an B, then this is going to fail to locate the proper boundary points, an we'll need something more complicated to locate the boundary points -- MrShort ----------------------------------------------------------------------- MrShorty's Profile: http://www.excelforum.com/member.php...fo&userid=2218 View this thread: http://www.excelforum.com/showthread.php?threadid=47284 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
interesting interpolation
Unfortunately the A and B series are not a interger or simply defined series of numbers. I see where you were going with the code though and it has given me a little idea so it is back to the drawing board for both of us.... :P -- nelg ------------------------------------------------------------------------ nelg's Profile: http://www.excelforum.com/member.php...o&userid=27771 View this thread: http://www.excelforum.com/showthread...hreadid=472841 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
interesting interpolation
The data in your table is produced by A*B
so in your results table, you only need to multiply the two values in each row for the result. -- Regards, Tom Ogilvy "nelg" wrote in message ... G'Day guys, need some help with this code. am trying to do a graphical interpolation by VB. I have attached a simple example of what I need. The values column need to be filled! Any idea? So far I can get the initial cells. For example for the first two values I can get out 2 and 3 in the A axis and 4 and 5 in the B axis. But keep getting errors everytime I try and get the four cells which the above 4 values intersect at (as soon as I can get those intersecting cells it is simple maths, no problems). Thanks in advance for any ideas. +-------------------------------------------------------------------+ |Filename: Interpolation example.doc | |Download: http://www.excelforum.com/attachment.php?postid=3875 | +-------------------------------------------------------------------+ -- nelg ------------------------------------------------------------------------ nelg's Profile: http://www.excelforum.com/member.php...o&userid=27771 View this thread: http://www.excelforum.com/showthread...hreadid=472841 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
interesting interpolation
I attached a sheet because it made it easier to explain. Below is a mini representation of what I need. B 1 2 3 4 5 ------------------- 1 2 3 4 5 |1 | 2 4 6 8 10 |2 | 3 6 9 12 15 |3 | A 4 8 12 16 20 |4 | 5 10 15 20 25 |5 | given two values, say, A=2.3 and B=3.5 find the interpolated value in the above table. The number that should come out is - assuming straight line interpolation between points - 8.05. I can get the limiting numbers in the A and B axis (2 & 3 and 3 & 4 respectively) but cannot get the intersepting values (6, 9, 8 & 12). With these second lot of numbers I can easily work out the final value (it is simple math). I just cannot get the numbers! Hope that is clear. -- nelg ------------------------------------------------------------------------ nelg's Profile: http://www.excelforum.com/member.php...o&userid=27771 View this thread: http://www.excelforum.com/showthread...hreadid=472841 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
interesting interpolation
Tom, The A*B is just a simple representation. The actual sheet has no trending values between A and B. It is the process I am looking for. Thanks for you comments so far though! Glen. -- nelg ------------------------------------------------------------------------ nelg's Profile: http://www.excelforum.com/member.php...o&userid=27771 View this thread: http://www.excelforum.com/showthread...hreadid=472841 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
interesting interpolation
I have custom function wrrtien by me
Say in this l1,l2 are values of A that are available in the matrix d1, d2 are the nearest values B available in the matrix dx is interpolation required in ranne B Function Intepolete(l1, l2, d1, dx, d2) If l2 l1 And d1 d2 Then Intepolete = l1 + (l2 - l1) / (d1 - d2) * (d1 - dx) End If If l2 < l1 And d1 d2 Then Intepolete = l1 - (l1 - l2) / (d1 - d2) * (d1 - dx) End If If l2 < l1 And d1 < d2 Then Intepolete = l2 + (l1 - l2) / (d2 - d1) * (d2 - dx) End If If l2 l1 And d1 < d2 Then Intepolete = l2 - (l2 - l1) / (d2 - d1) * (d2 - dx) End If If l2 = l1 Or d1 = d2 Then Intepolete = l1 End If End Function In this i have interpolation in one side say A, you need from both sides So enhance the code for that. I use this function as custom function on worksheet, using absolute and mixed refrences I can mange to interpolete in variety of ranges, but it has only one direction of interpilation. "nelg" wrote in message ... Tom, The A*B is just a simple representation. The actual sheet has no trending values between A and B. It is the process I am looking for. Thanks for you comments so far though! Glen. -- nelg ------------------------------------------------------------------------ nelg's Profile: http://www.excelforum.com/member.php...o&userid=27771 View this thread: http://www.excelforum.com/showthread...hreadid=472841 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Interpolation? | Excel Worksheet Functions | |||
| interpolation | Excel Discussion (Misc queries) | |||
| Interpolation | New Users to Excel | |||
| help with interpolation and limit of interpolation | Excel Discussion (Misc queries) | |||
| Here's an interesting one... | Excel Programming | |||
Hybrid Mode
