+ Reply to Thread
Results 1 to 5 of 5

Creating a chart that shows distribution of coordinates

  1. #1
    Registered User
    Join Date
    02-10-2020
    Location
    Cheshire, England
    MS-Off Ver
    365
    Posts
    7

    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
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,703

    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: copy to clipboard
    Please Login or Register  to view this content.
    for column J almost the same (I just skipped moving right)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    well, I truncated formula even more to have labels for each triplet (column K)
    Formula: copy to clipboard
    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: copy to clipboard
    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.
    Attached Images Attached Images
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,703

    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
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-10-2020
    Location
    Cheshire, England
    MS-Off Ver
    365
    Posts
    7

    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. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,703

    Re: Creating a chart that shows distribution of coordinates

    Glad to hear that. As I think it takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] 100% stacked bar chart shows wrong distribution - axis not the issue
    By Anine in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-02-2018, 04:25 AM
  2. [SOLVED] Creating a Dynamic Chart that shows from Today for 100 days forward, changing daily
    By Robbie8 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-26-2016, 08:13 PM
  3. Creating normal distribution Chart in Excel
    By sadafsh in forum Excel General
    Replies: 4
    Last Post: 09-12-2012, 01:03 PM
  4. How can I make a graph that shows the data distribution?
    By megashock in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 03-02-2011, 09:12 AM
  5. creating a chart with x-coordinates in different columns
    By Mux08 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2008, 04:21 AM
  6. Creating a distribution chart
    By steve65 in forum Excel General
    Replies: 5
    Last Post: 08-21-2006, 08:07 PM
  7. X,Y coordinates on chart
    By lgarcia3 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-17-2006, 07:46 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1