interesting interpolation

[nelg]

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

 

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

 

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

 

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

 

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

 

[MrShorty]

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

 

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

 

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