+ Reply to Thread
Results 1 to 55 of 55

plot dots on a map with data

  1. #1
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    plot dots on a map with data

    good afternoon all,

    im planning on creating a map of a badminton court.
    im hoping to record each hit of the shuttlecock with a dot on the map. Of course I want this to be inputed by data rather than manually inserting a circle shape and placing it in the correct location each time.

    is there any way to do this in excel?

    thanks

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: plot dots on a map with data

    Plot an X Y (Scatter) chart from your data, with your badminton court map as the chart background. Adjust axis scales to suit your map / data.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    thanks that works really good.
    i was also hoping for an arrow option to show where a hit started and where the ball ended up.
    again, it would need to be plotted through data rather than manually.

    thanks

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: plot dots on a map with data

    If you are just plotting pairs of points, you can add a linear trendline to the X Y Scatter series, and format it with an End Arrow. See attachment for an example.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    thanks olly.

    just had a play with your attachment.
    when i add more data points they dont appear as lined pairs. in fact it completely messes up the 2 arrows you already have

  6. #6
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: plot dots on a map with data

    As I said, you need to plot pairs of co-ordinates as individual series in the XY Scatter chart.

    So, in my attachment, A2:B3 contains Series 1, D2:E3 contains Series 2...

  7. #7
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    so each arrow wont automatically be plotted with each row's data?
    i was hoping this would be the case.

    is there any way to make this happen without doing anything more than inputing XY data?

  8. #8
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: plot dots on a map with data

    Double click on each stroke in range C1:H8
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  9. #9
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: plot dots on a map with data

    Not sure how you want it to work - how are you identifying which pairs of co-ordinates you want 'linked' with an arrow?

    Can you attach a workbook showing your data structure, some example data, and what your required outcome looks like.

  10. #10
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    i have no workbook yet. im working off yours which is perfect for what i want to do.

    i would like each row to represent an arrow.
    so columns A,B,D,E will form an arrow, exactly how you've done with the first 2 arrows.

    however, i would like many many more arrows. so im hoping i can simply continue to input data (e.g. row 4, row 5, row 6...) and each of the rows will represent a new arrow.

    thanks for you effort olly

  11. #11
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: plot dots on a map with data

    Check popipipo's attachment in post #8 - looks like he's understood your requirement better than me!

  12. #12
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    popipipo had a nice idea but it doesnt work. it connects all the lines together. i want separate arrows for each data point

  13. #13
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: plot dots on a map with data

    You are going to have to show us how you want to organise your data and what the resultant chart looks like, then, as I can't picture what it is you really want.

  14. #14
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    exactly how you have done it olly in your attached workbook.
    i simply want to add many more arrows. thats it!!

    how can i do that?

  15. #15
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: plot dots on a map with data

    it connects all the lines together
    That is what happend in a real match.

  16. #16
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    Quote Originally Posted by popipipo View Post
    That is what happend in a real match.
    im not going to be displaying all hits in a match. only the scoring points which are obviously not connected.
    any ideas?

  17. #17
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: plot dots on a map with data

    im hoping to record each hit of the shuttlecock with a dot on the map.
    In your first post you said something different

  18. #18
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    they are 2 different things. i prob should have started a new thread.

    my first question was how to plot dots on a map.
    the second is how to pair the dots and make an arrow

  19. #19
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    just realised my huge mistake.
    i thought each arrow was based on the data on each row but its actually column A and B is one arrow whilst D and E is the other.

  20. #20
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: plot dots on a map with data

    I'm going to say ONE more time:

    Please attach a workbook, showing how you want to enter and store the data, and what your required outcome looks like based on that data. Your requirement is still ambiguous. Make it crystal clear, then we can probably help.

  21. #21
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    See example for data layout in order to get break in line and arrow head on line end
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  22. #22
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    Quote Originally Posted by Andy Pope View Post
    See example for data layout in order to get break in line and arrow head on line end
    thats a nice effort andy pope. however, i dont want the liness attached to one another - they should be seperate.

  23. #23
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    The lines are not actually joined. They only appear joined as the finish point is the start of the next arrow.
    If you only want to track the shots for 1 player rather than the journey of the shuttlecock then simply enter different values
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    hi andy pope.
    ive been really busy at work so didnt have time to check your attachment out properly.
    YOURE A STAR!!!
    thats exactly what i was looking for.

    one tiny adjustment, if possible. can each arrow's data be on one row (see my attachment)?
    plus i do not want there to be any spaces between each arrow's data (again, see my attachment).

    thanks
    Attached Files Attached Files
    Last edited by tlacloche; 04-03-2014 at 11:57 AM.

  25. #25
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    No the chart data need to be laid out differently to your input data.

    You can use formula to prepare chart data.
    Attached Files Attached Files

  26. #26
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    that's a shame.

    so to clarify...there is no way to have the 4 data plots for each arrow to be all on the same row
    also, there has to be a space between each arrow's data plots.

    surely a tweak of the formula can make this possible...

  27. #27
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    The gap is required to break the line and draw the arrow head between each pair of data points.

    Technically it's possible to keep your data arrangement but it will be a nightmare to extend.

    New data layout series formula
    =SERIES(Sheet1!$K$1,Sheet1!$J$2:$J$9,Sheet1!$K$2:$K$9,1)

    A new data point will increase the row 9 reference
    =SERIES(Sheet1!$K$1,Sheet1!$J$2:$J$12,Sheet1!$K$2:$K$12,1)

    Old data layout series formula
    =SERIES(Sheet1!$K$1,(Sheet1!$B$2,Sheet1!$D$2,Sheet1!$F$2,Sheet1!$B$3,Sheet1!$D$3,Sheet1!$F$3,Sheet1!$B$4,Sheet1!$D$4,Sheet1!$F$4),(Sheet1!$C$2,Sheet1!$E$2,Sheet1!$F$2,Sheet1!$C$3,Sheet1!$E$3,Sheet1!$F$3,Sheet1!$C$4,Sheet1!$E$4,Sheet1!$F$4),1)

    A new data point will require 6 new cell references.
    =SERIES(Sheet1!$K$1,(Sheet1!$B$2,Sheet1!$D$2,Sheet1!$F$2,Sheet1!$B$3,Sheet1!$D$3,Sheet1!$F$3,Sheet1!$B$4,Sheet1!$D$4,Sheet1!$F$4,Sheet1!$B$5,Sheet1!$D$5,Sheet1!$F$5),(Sheet1!$C$2,Sheet1!$E$2,Sheet1!$F$2,Sheet1!$C$3,Sheet1!$E$3,Sheet1!$F$3,Sheet1!$C$4,Sheet1!$E$4,Sheet1!$F$4,Sheet1!$C$5,Sheet1!$E$5,Sheet1!$F$5),1)

  28. #28
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    could you please put it into practice with an attachment.
    im not too skilled in excel to fully comprehend your message

  29. #29
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    even more reason to crearte a data layout the chart can understand
    Attached Files Attached Files

  30. #30
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    so youre saying i need to drag down the formulas in column J and K in order for me to add new arrows by inputting data in columns B-E

  31. #31
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    If you want to add more data points then you need to extend the data used by the chart, regardless of data layout.

    If you use the data layout in J:K then that picks the values from the data layout in B:E and lays it out in a way that makes it easy to extend the data series range.

    If you stick with the B:E layout you will need to use the Select Data dialog and CTRL+Select each new data point, including an empty cells to break the line.

  32. #32
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    okay im beginning to understand.

    i want to simply add new data points in the B:E format which will automatically add new arrows to the chart.
    for this to happen i HAVE to use the J;K layout to pick the values from B:E.

    is this correct?
    so there is no way for a chart to automatically update with new arrows from only the B:E format?

  33. #33
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    Correct. No auto update from data in B:E

    You can use that layout as your data entry. Then with formula in H:K you can convert the data layout for charting.

    In the new attachment you simply add data to B:E table. Copy block of 3 rows to end of H:K and chart will automatically update because of the use of named ranges.
    Attached Files Attached Files

  34. #34
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    thanks andy.

    any idea how to make the B:E table not give randomised numbers? i would like to simply add my own data plots.

  35. #35
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    Is that a trick question?

    Simple type you numbers in the cells. That will replace the RAND formula with static numbers

  36. #36
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    when i type a number in the column B cell, the cells in C:E automatically produce randomised numbers. i want them to remain empty until i put a number in them.

  37. #37
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    Then delete them. The only reason they are there is for example data as you would not provide example when requested.

  38. #38
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    apologies. was overthinking the whole thing.

    thanks

  39. #39
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    Hi Andy,
    i was having a play around with your chart (which by the way i love. thank you), and i thought of the idea of having different coloured arrows belonging to a different data set.

    so for example, currently the chart displays blue arrows from the table B:E.
    i was hoping another table can be created which also plots arrows on the same chart, but in red.
    is this possible?

    thank you so much if you can help.
    Attached Files Attached Files

  40. #40
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    Add anothing set of Y data and include a formula to test for line colour. I've added line colour to existing table of data.
    Attached Files Attached Files

  41. #41
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    thats amazing. thanks.
    Last edited by tlacloche; 05-10-2014 at 06:53 AM.

  42. #42
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    The Yellow and Light Blue arrows are facing the wrong way.
    Please see the highlighted coordinates in green.

    I have no idea why the are doing that.

    Thank you
    Attached Files Attached Files

  43. #43
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    For what ever reason the Begin and End style for each series are not the same.

    Check the settings for correct series and apply same Begin and End formatting to the other series.

  44. #44
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    For what ever reason the Begin and End style for each series are not the same.

    Check the settings for correct series and apply same Begin and End formatting to the other series.

  45. #45
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    i think it is the same. when i right click the arrow and go to "Format Data Point", under line style the Begin Type is a straight line whilst the End Type is the pointed arrow.

    where exactly are you talking about

  46. #46
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    Red line, Begin Type:=None, End Type:=Arrow 1

    Yellow line, Begin Type:=Arrow 1, End type:=None

  47. #47
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    please tell me where youre seeing this.
    When i right click the yellow arrow and follow the procdure from my previous post it shows that Begin Type is "No Arrow" and End Type is "Arrow".

  48. #48
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    See screen shots for yellow and red line dialogs.
    Attached Files Attached Files

  49. #49
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    mine is the exact same but its still the wrong way round.
    maybe something on my computer is somehow interfering with it.

    it doesnt make sense

  50. #50
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    You have Mac office?

  51. #51
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    no. using windows.

  52. #52
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    ill delete the chart and start a new one and see what happens

  53. #53
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    So what is verison Excel 2011?

  54. #54
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: plot dots on a map with data

    Created a new chart and it works now.
    Maybe the previous chart was corrupted somehow.

    THANK YOU SO MUCH FOR YOUR TIME

  55. #55
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: plot dots on a map with data

    I would guess you formatted a data point rather than the data series.

+ 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. Scatter plot display count as dots
    By nkpriya in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-13-2013, 05:47 AM
  2. Plot the line of a function and also scatter dots on the same chart
    By zman44 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-05-2013, 08:54 AM
  3. Replies: 2
    Last Post: 07-26-2012, 09:27 AM
  4. How can I plot multiple data set on a single contour plot
    By hafziebone in forum Excel General
    Replies: 0
    Last Post: 02-10-2012, 11:27 PM
  5. dot plot - single x axis location with multiple y axis dots
    By EJF in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-22-2006, 10:15 PM

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