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

Do you mean that you want to have your Soundings to start at 0.7 and
increase in 0.1 increments to, say, 8.0, and for each of these values
you want to extrapolate between the appropriate pairs of data in your
original table (i.e. data on either side of the new soundings
increment)?

If so, I'll have a look at it later on.

Pete

On Aug 1, 3:33 pm, "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

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

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

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 -

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

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

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