Formula to label the points of a polygon.

Hi,

I've got a challenge that only an excel wizard could complete!

Given 5 pairs of x & y coordinates, I need to label the points consistently by formula!

I have determined that point "A" will be the x,y coordinate with an x value closest to the x value of the center of the polygon.

I've attached a worksheet with 5 sets of sample data, all being 5 sided polygons. The polygon's points are in the yellow columns and I need to fill in the Label column with the labels "A", "B", "C", "D" & "E". Please ignore the grey columns. Thanks.

The center x,y coordinate is just the average of the 5 coordinates but what kind of formula would I use to label the other points consistently, preferably clockwise from point "A"?

Any help is appreciated.

Bye

no attachment

relax, the attachment is there.


Alright, I think I worked out some logic for it:

If point A is above the center point then:

Point A must be to the right of E and the left of B
Point B must be to the right of A and above C
Point C must be below B and to the right of D
Point D must be to the left of C and under E
Point E must be above D and to the left of A

If point A is below the center point then:

the opposite of the above 5 conditions holds:

Point A must be to the left of E and the right of B
Point B must be to the left of A and below C
Point C must be above B and to the left of D
Point D must be to the right of C and above E
Point E must be below D and to the right of A

WOW! This is getting deep! Anybody like a challenge?

Alright, I was able to get the first label "A" with the formula:

Please Login or Register  to view this content.

in H2 with CTRL-SHIFT-ENTER and copied down

and

Please Login or Register  to view this content.

in F2


but that's just one label. I need a formula that determines which one of the five labels the coordinate pair is. Do I need to do a 5 deep nested IF statement?

Here's a logic table beside 5 pairs of x & y coordinates. Ignore the shaded areas please. How do I get the pairs to fit into the table in the right spots? I can determine which pair is "A" but then how do I proceed?

wHOA, Do I have to do DGET? I've never used that before. Help!

okay, let's try this:

how would I get excel to match the four pairs below to these conditions:


............ X................Y
=A -16.23...............8.71
=B <j2 & <j5.......<k5
=C <j5 & <j6.......>k2 & >k6
=D >j3 & >j4.......>k2 & >k3
=E >j2 & >j4.......<k4

and

If j4 > j3 then k3 < k4
If j5 < j6 then k6 < k5
If k3 < k2 then k6 > k2
If K6 < K2 then K3 > k2

-3.48.............. 20.06
-21.88...............7.54
4.07...............18.44
-20.83..............11.47

Rethink, Okay what I'm actually trying to do is given a point that isn't A, what point is it?

Well, if A is below the average of the center and my point is to the left of A, then it's B or C, right of A is D or E.

there are only two coordinate pairs that are B or C, my point is one of them, so comparing my point to the point that is different than itself,

if my point's x is > that x and my y is < that y then my tag is C

or

if my point's x is < that x and my point's y is greater than that y then my tag is B


alright, I'm getting there. Can you beat me?