Creating a scatter-plot with series from row values, and XY values from two other rows

1
votes

I am doing a project that requires me to study the several condition that affect GPS accuracy, and after I collected a set of data and dumped it to Excel, I was trying to plot a scatter graph, grouping the data into different series according to a value: in this case, I wanted to plot the Latitude and Longitude values as the XY scatter values, and separate the series by the number of satellites when the fix was obtained.

Timestamp     Latitude    Longitude #Satellites
133009.279    3839.3354   904.7395  0
133010.279    3839.3354   904.7395  0
133011.279    3839.3354   904.7395  0
133026        3845.9863   907.4513  4
133027        3845.986    907.4491  4
133028        3845.9851   907.448   4
133222        3845.9909   907.4866  4
133023.28     3845.9817   907.4429  5
133024.28     3845.9867   907.4549  5
133048        3845.9868   907.452   5
133205        3845.9929   907.4858  5
133206        3845.9927   907.486   5
133207        3845.9925   907.4862  5
133056        3845.9885   907.4569  6
133057        3845.9881   907.4578  6
133223        3845.9905   907.4868  6
133224        3845.9901   907.487   6

I have tried selecting the three rows, adding the series afterwards by selecting the appropriate row, and even tried pivot tables, but these don't allow for scatter-plots unfortunately.

All this to no avail, but I am positive that you can plot the graph. Does anyone have an idea?

PS: Manually selecting the series myself isn't an option, since there is a large number of data. If I could select all of the data for one specific value in a row, though, would let my select each series, and I think I would be able to make it from there.

1
excelgraphicschartsscatter-plot
It would help to update your question with a small sample of the data you're trying to plot. How many points per series? – Tim Williams
Here's a link to the spreadsheet link The points per series may vary, since I cannot count all of them manually, and are probably different for each number of satellites. – ravemir
Edited to add sample data - much easier to see what the question is asking this way. A good start would be to sort by #satellites, then loop throught the rows, adding a new series every time the #satellites changes. If you're not looking for a macro solution then I'm not sure how else you'd do this. – Tim Williams
That would mean that I had to copy the data if I wanted to order by another row, like timestamp. I thought Excel was better than this. Thanks anyway, this will have to do :) – ravemir
For the purposes of the chart you want to create, would sorting by time stamp change how it looks? It is possible to get "better" functionality from Excel, but often it will take some level of macro coding to get there. – Tim Williams

1 Answers