distance between points to matrix form..

Hey guys, somewhat of a noob at vba and need a little help with a small problem...

I have a series of points with x and y co-ordinates and I need to find the distance between all of the points and each other. The number of points will change and this needs to be taken into account in the vba code. I tried using an next statement with a loop, but really struggling. Also it is subject to the following formula:



gh = C0 + C * [1.5(h/a)-0.5*(h/a)^3] for h<a..

gh = C0 + C h>a

gh = o h = 0



Where gh "gamma h" is what i ultimately need to work out, after working out h, Co and C are values given, h is the distance between the points, which will need to be put in the formula and a is a given number (a distance). So a simple if statement could suffice for these equations depending on what the distance between points are...



I have entered some values into the spreadsheet to show what i am talking about, you can find the excel spreadsheet attached. I am sure it is quite simple but I cant get my head around it...I need all the gh values to be entered into a matrix kind of form, this can be seen in the attachment..

Any help at all would be greatly appreciated.

Thank you

Originally Posted by Richard Buttrey

Hi,

The attached is one solution. Just double check the master formula for brackets and precedence in J2 on the Points in Block sheet.

Regards

Richard, thanx for that, it isnt exactly what I was looking for though, its great how the size of that grid changes but the formula doesnt calculated what i need....
The h values isnt the points 1, 2, 3, 4. The h value is the distance between teh point 1 and 2..So for 1, 2 in this example 1: x = 60, y = 87, 2: x = 105, y = 87. So the distance between them would be SQRT((X1-X2)^2+(Y1-Y2)^2): h = sqrt((60-87)^2+(105-87)^2) which would give h = 45 m...I then need this h value in the equation to find gh.....Does this make any sense???
I have show what the 4 point matrix should look like, so that it might be easier to understand... I have shown the h values, then the gh values in two different tables, though I do need only the gh values in the major table....Thank you so much for all the help!!

I have fiddled with this a bit and still cant quite get it to work out, it is close to being what i need but i cant quite get it, I changed the formula in the J2 cell, but I cant seem to get it working...I attached the new file, thank you

Originally Posted by Andy Pope

From the point of view of the OP (that's you) yes more forum means more helpers chasing your problem trying to help for free.

From the helpers point of view without the knowledge that you have others tackling your problem you are simply wasting their time.

Read the form rules and it explains why cross posting is a inconsiderate thing to do.

Yeah I couldnt agree more, I didn't even think about it like that, I should have read the rules more carefully in the first place, I am just all very new to this. I shall put a post on the other site about this one and make sure that when/if it is solved I will inform both sites...
Is that what I should do??
Thank you

Thank you Andy
Ok so back to it....
From my last post about the problem, I just cant work out the table, as the values dont quite work, pretty tricky...

Originally Posted by Andy Pope

better to use 2 tables, although you could do it in 1.

You just need to use an INDEX function to return the correct X and Y values.

That is great what you have done, when i click build table though it is all erased, I think I just need to fiddle with it a little bit....
Thanx for that mate

Just going over this all, the matrix works great but when there is a gap in the numbers it wont work, this is due to the "Only Points Inside", is there a way to use what Andy Pope has done but just so that the values that are actually inside the polygon will be calculated in the matrix, so basically is there a way to move the values up one, so they are one after another with no gaps....
See the attachment, in the "Drilling" Worksheet in the "Only Points Inside" column how can I move them up or display them one after the other so teh values S22:T24 will move up so then they will work in the Points in Block worksheet to find the gamma h values...
Thanx guys...

Still pretty stuck with this guys....Any ideas?