Hi Andy,

thanks for feeding back with this - one for the archives, I think.

Pete

On Aug 8, 12:19 pm, "andy duncan"
wrote:
Pete,

I had some feedback from a Colleague also looking into the problem :

"Ages ago I wrote a function to interpolate values from a table, and I use
this all the time now. If you see the attached spreadsheet, I have two extra
sheets - the first uses my interpolate function, and the second uses
Microsoft's Lookup function.

Basically, I create a new table, with iterations of 0.1m, and lookup the
nearest hydrostatic mass values to a particular depth. I then interpolate
the values to find the correct hydrostatic data for that particular depth.

The formula looks like this :

=interpolate($B10,Orig.Data!$A$2:$A$21,Orig.Data!$ B$2:$B$21)

interpolate is the name of my function. In this example, B10 is the target
depth, and then I have two ranges. The first range is the original depths,
and the second range is the original hydrostatics. My function looks up the
new depth against the original depth, and interpolates to calculate the new
hydrostatics.

This function works quite well, but sometimes needs tweaking, depending on
the application.

Function Interpolate(Target, Range1, Range2)

' This interpolate 2 function looks down the list until just past our
target. Then grabs the value before our
' target, and interpolates.

Dim Val1, Val2, Ans1, Ans2 As Single

' If we go over the maximum, we don't bother to run this function
If Target Range1.Rows(Range1.Rows.Count) Then
Interpolate = ""
Exit Function
End If

Val1 = 99999999999#
Ans1 = 0
Val2 = 99999999999#
Ans2 = 0

For t = 1 To Range1.Rows.Count
If Range1.Rows(t) = Target Then
Val1 = Range1.Rows(t)
Ans1 = Range2.Rows(t)
If Val1 = Target Then
Interpolate = Ans1
Exit Function
End If
Val2 = Range1.Rows(t - 1)
Ans2 = Range2.Rows(t - 1)
Exit For
End If
Next t

' If Val2 - Val1 = 0 Then
' interpolate = 999
' Else
Interpolate = Ans1 + ((Ans2 - Ans1) * (Target - Val1) / (Val2 -
Val1))
' End If

End Function

Useful for you ?

Andy

"Pete_UK" wrote in message

ps.com...



You're welcome, Andy - thanks for feeding back. Perhaps you can post
back with your solutions if you do manage to fine-tune it further.

Pete

On Aug 2, 12:57 pm, "andy duncan"
wrote:
Thanks for that Pete,

I too had something similar to that approach but got caught up in my own
confusion !

Oddly enough when I compare the results against a similar table that
someone
has hand cranked there are maturally differences. Once I graph the
differences, a pattern is visable (Zig zagging above and below the Zero
datum ( +/- 12 approximately).

Also some of the values that are 'known' in column A to exist in B, do
not
come up as values in F when referenced from D.

Oh well that is approximations for you.

Thanks for your efforts, and I will see if we can fine tune it a little.

Andy

"Pete_UK" wrote in message

groups.com...

Andy,

with your example data (plus headings) occupying A1:B16, I put these
headings in D1 - "S", E1 - "Locn" and F1 - "M".

I then filled D2 down with numbers from 0.7 to 8.0 in 0.1 increments
(although I suppose you could start at 0.1 if you wanted to - the
start and finish values must be within the first and last data items
that you have). Then I put this formula in E2:

=MATCH(D2,$A$2:$A$16)

and copied this down - this just finds where in the data the new
increment would be located. I then put this formula in F2 and copied
down:

=(INDEX($A$2:$B$16,E2+1,2)-INDEX($A$2:$B$16,E2,2))/(INDEX($A$2:$A
$16,E2+1)-INDEX($A$2:$A$16,E2))*(D2-INDEX($A$2:$A$16,E2))+INDEX($A$2:$B
$16,E2,2)

This just does a linear interpolation between the two points either
side of your new increment, but it will give you your "quick fix".

Hope this helps.

Pete

On Aug 2, 9:17 am, "andy duncan"
wrote:
I figured I may have to graft a mathematical equation together, and
insert
rows to suit etc.

Unfourtunately what I have in reality is 9 groups of Data : (Soundings
and
Mass), each ranges from 0 to 8.0m but each has a different incremental
'delta' of Soundings, upto 3 decimal places.

i.e. Data group one seems to have a stepped increase of 0.331, Data
group
two has a stepped increase of 0.472 etc etc.

I am trying to create a large table with soundings from 0 to 8.0m
every
0.1m
(i.e. 80 rows) and each column to represent the Data Groups one to 9
and
have the data fit as best to the new 'soundings' from the 'known'
soundings.

I can graph them out naturally but Excel can not pick out data from
user
choosen points (can it ?)

Like everything there - is more than one way to skin a cat, and I am
slowly
getting there the long way round. I am curious if there is a quick
fix, a
function for example that works?

I have been using Lookups etc but they only output the next known
quantity.

Andy

"Jerry W. Lewis" wrote in
...

If you are just wanting to interpolate, you should get good results
from
fitting
y = (a+b*x)/(1+c*x)
to the nearest 3 points (multiply both sides by (1+c*x) and you have
3
equations that are linear in the 3 unknowns). This rational linear
form
allows some curvature from linear, while preserving monotonicity.

If you want to extrapolate beyond the upper end of your data, good
luck!
The last point suggests a change in the relationship, but you have
precious
little data describing what happens there. Is there a theoretical
form
for
this relationship?

Jerry

"andy duncan" wrote:

I think I can reach my goal but with many complicated steps - all
probably
summarised in a simple function !

I have two columns of data :
Soundings, Mass :
0.000 0.000
0.772 65.750
1.335 129.640
1.897 198.700
2.460 271.350
3.022 346.640
3.585 424.020
4.147 503.130
4.710 591.880
5.272 695.640
5.835 800.290
6.397 905.730
6.960 1011.760
7.522 1118.300
8.085 1133.090

the iterations of my known column of data, (the soundings), is
first
0.7
and
therafter 0.3.

I need to examine the data to increments of 0.1, and hence spread
it
out
over far more iterations.

Any good ways of doing this that you know of ?

Cheers Andy- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -