interesting interpolation

[nelg]

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.

[STEVE BELL]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Tom Ogilvy]

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" <[email protected]> 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" <[email protected]> wrote in
message
> news:[email protected]...
> >
> > 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
> >
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[nelg]

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]

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.

[STEVE BELL]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in
> message
>> news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

[MrShorty]

HEre's a relatively simple solution that can be made to work on your sample data set.

1) Make the column of A values the leftmost column in the lookup table rather than the rightmost column. I'm going to assume lookup table is in the range A3:E7

2) Create a new table that looks like this starting in A11 (newA and 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 which you wish to interpolate. Then, as you say, the interpolation itself is simple.

Important note: This method only works if the defining A and B series in the lookup table are integer series {1,2,3,4,5,...}. It would be readily adapted to cases where the series has an easily defined pattern (say {0.5,1,1.5,2,2.5,...}). If there isn't a simple pattern to A and B, then this is going to fail to locate the proper boundary points, and we'll need something more complicated to locate the boundary points.

[nelg]

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

[MrShorty]

Rats, I was afraid it would be more complicated than the sample data set indicated.

I'm not real good with Excel's built in lookup functions, but I expect someone out there can figure out a combination of VLOOKUP, HLOOKUP, INDEX, MATCH, etc. that will extract the desired boundary points. If they do, I expect it will be a complex, ugly looking function.

If it were me, I'd probably build a UDF to handle this scenario. Something like:

Function 2DLINTERP(lookuptable as range, newA as double, newB as double) as double
rowA=0
'loop through rows to locate the interval containing newA
Do
rowA=rowA+1
loop until lookuptable.cells(rowA,1).value >= newA
colB=0
'loop through columns to locate interval containing newB
Do
colB=colB+1
loop until lookuptable.cells(1,colB).value >= newB
'now boundary points are located in lookuptable.cells(rowA-1,colB-1) through lookuptable.cells(rowA,colB)
2DLINTERP=interpolation formula
end function

Note that this function assumes that A and B are sorted in ascending order, and doesn't have any code to deal with cases where newA or newB are outside the range of A or B. I'll let you decide how to deal with those cases. I haven't tested it either, so it will probably need some debugging.

That should be a start for you.

[nelg]

Thanks a heap for that. With those posts plus some things I have thought up I think I have it sussed.

[new.microsoft.com]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>