Construct boundaries (as a polygon) in excel chart for a custom data

[ajaysomasi]

Hi, I have a task to construct polygon boundaries for specific data.. Have a large number of such polygons to construct hence any help as a VBA or directly in excel is much appreciated. I was trying to construct a polygon using scatter plot but the lines are joining to points that will form a boundary. I tried sorting the data to bring a polygon, but guess I'm missing something.. VBA should help but cant code.. I need a standard code that I can run as there are many such boundaries to be constructed.

Datapoints are shown below.. Please help..

-Ajay

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]X
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]25
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]31
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jon Peltier]

PGC -

Yeah, I saw what you were doing, but from the original it wasn't really clear to me what was the end product.

 

[pgc01]

... it wasn't really clear to me what was the end product.

I must confess that I'm not completely sure either if this is what was originally asked.

It's a pity that we don't have feedback from the OP.

 

[Gerald Higgins]

Hey guys, it's still an interesting discussion (IMHO), even if we don't know that it's relevant to what the OP actually wanted !

Jon, one thing that I think is worth stressing with your solution (which you did hint at) is that the shape of the polygon depends on the sequencing of the points.
In your example, you've essentially determined the sequencing yourself in such a way that a reasonably pleasing polygon is created.
But any number of slightly different polygons could also be generated by shifting one or two points up or down in the sequence.

If the question is really "How do I generate a polygon where every set of given co-ordinates is a vertice on the polygon" then we need to address sequencing somehow.
The sequencing might be a logical feature of the data - for example time.
If not, then we either need to build an algorithm for doing it, or manually select the sequence by making what are essentially arbitrary aesthetic decisions, or we need to leave it random, which generates a big mess.

Sticking with the original 10 data points (and duplicating the first/last one as Jon did) . . .

This sequence generates a different polygon which is arguably just as valid as Jon's
[TABLE="width: 80"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]


And this sequence has the position of all points (except the first and last) randomised, and generates "a big mess".
[TABLE="width: 80"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[/TR]
</tbody>[/TABLE]


Is there an algorithm of some kind for generating an acceptable polygon like this ?

 

[pgc01]

Is there an algorithm of some kind for generating an acceptable polygon like this ?

Hi

Just a comment.

If you don't go with the convex solution then you have many possible solutions.

They are not necessary a big mess, but they may be different and, I'd say, equally acceptable.

For ex., just 4 points: (0,0), (1,1), (2,1), (0,2)

Solution 1:

0 0
1 1
2 1
0 2
0 0

Solution 2:

0 0
2 1
1 1
0 2
0 0

I don't think one is more "acceptable" than the other. They are just different.