# Creating a chart that shows distribution of coordinates

1. ## Creating a chart that shows distribution of coordinates

Hi

Like many challenges with Excel explaining the problem is probably the biggest, so here goes:

I have a number of circular bridge support columns that we surveyed a couple of years ago as the client is worried about the verticality of them. So each column was surveyed relative to a fixed origin and relative to a true vertical from the base of the column. So we have a coordinate at the bottom (VC) and a coordinate at the top (FC). Using this data I have calculated the X-Y difference and from that a vector and a bearing angle as one of the issues could be that if all the columns are leaning in the same direction then we may have a problem as opposed to them all going in random directions, which is less of an issue.
We have been back earlier this year to carry out the same exercise, all the data is calculated and tabulated in Excel, which is OK but what I would ideally want is some sort of chart that shows each column and the direction it is leaning based on the data I have i.e X-Y coordinate, vector and bearing angle. I could then compare the data from the two surveys and see at a glance if there is any trend to any one direction.
I have looked at polar charts, Radar charts and even wind rose charts, but I cant seem to get anything that makes sense.

In would like to see an angle scale (say in 15deg intervals) and each column shown as a point or a line from the origin.

I have attached a sample of the data, this is what the columns mean:

X diff Difference between x coordinate at the bottom of the column
Y diff Difference between Y coordinate at the bottom of the column

Horizontal Vector dist The hypotenuse calculated from the x and y difference.

Horizontal Bearing angle The angle relative to North based on the above values.

Hope someone can help with this

Thank

Tim

2. ## Re: Creating a chart that shows distribution of coordinates

Here is my idea of graphic presentation in such case:

Prepare dummy series of data with triplets of pairs of coordinates
1) some central point
2) point at offset xdiff and ydiff from this central point
3) pair of #NA! errors
and next triplet but for second pair of xdiff, ydiff (for second column)
4) some central point (i used one moved to right at constant distance
5) point at offset xdiff and ydiff from this central point
6) pair of #NA! errors
and so on

such a formula could be used to find such triplets (see column I in attachment)
Formula:
`Please Login or Register  to view this content.`
for column J almost the same (I just skipped moving right)
Formula:
`Please Login or Register  to view this content.`

well, I truncated formula even more to have labels for each triplet (column K)
Formula:
`Please Login or Register  to view this content.`

After making scatterplot (but no point markers, only interconnecting lines) and playing with it's size and axes it looks pretty nice to me
I made also second approach which is closer to polar plot idea - it's in the copy of original sheet
The only thing changed is formula in K. It now locates description near the offseted point
Formula:
`Please Login or Register  to view this content.`

And of course I played a bit with graph size and axes again

See the screenshot and the file.

3. ## Re: Creating a chart that shows distribution of coordinates

I'm not sure if it fits your needs, but if the second approach is fine, I played a bit with location of labels. They are now located at extra series and placed at some (parametrized) distance from endpoint of line. Please let me know if this meets your needs.

PS. The offset coould be 0 and then scale at graph axes set to for instance -0.05... 0.05

4. ## Re: Creating a chart that shows distribution of coordinates

Hi Kaper

Sorry for the delay in replying.
That is exactly what I was looking for, however as I was in a hurry I decided to just plot the coordinates in CAD and draw the vectors. Bit of a pain in the **** to be honest, but the results are the same. Obviously your method is much more favourable and I will be trying it out later, I can already think of a few other situations were this can be used.

Many Thanks

Tim

5. ## Re: Creating a chart that shows distribution of coordinates

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Best,

Kaper

There are currently 1 users browsing this thread. (0 members and 1 guests)