Excel help here too?

[Eli]

How do you create an XYZ graph in Excel?

I'm trying to plot my speed over altitude like the MyTracks app and/or Google Earth will, like this:

http://home.comcast.net/~epruett3/GoogleEarthCap.jpg

Here's the Excel file:

http://home.comcast.net/~epruett3/Sa...%20Phoenix.xls

Thanks for any.. Insight?

[Need4Speed]

Insert, chart, line, next, columns, next, title, x, y, next, as object in, finish

This will generate a chart.
Then right click on the white space in the chart, then select source data.
Data range is the data you want to plot, in your case select all of the cells including the first row. Do this by clicking on the small icon to the right of the “data range” box. For that much data I would only select the first 20 rows or so, then later you can change the range by looking at the last row number, and editing the chart data range from say row 21 to 31723.
Click on the small icon to the right of the “source data – data range” window.
Click source data, then the small box to the left of the data range box, then cell 2 through say 20,
Left click, then Right click on any of the plot lines that don’t have the proper left side scale (speed), select format data series. Select the axis tab, then secondary axis, then ok.
You should now have a scale on the left and on the right. You can then right click on the scale to select the min and max range for the scale.
For the second plot I did I had to turn “markers” off for each plot, click on the plot, the right click, format data series, then marker none.
It takes a lot of playing around to figure out Excel, just make sure to keep a back up copy, save early, save often, add the time and date to the file name so you don't lose your work.

If you were able to get distance samples that could be used as the x axis and would be great. I will email you the updated excel file I have, I have no idea how to post a 5MB file.

[samwichse]

Beat me to it!

[Eli]

Thanks Dave. I'm familiar with Excel on a basic level, and can create basic charts and stuff, just didn't know how to make two scales. I'll play around.

It doesn't look like the CSV generated has distance in it. I guess it could be calculated from speed/time.

[Need4Speed]

Forgot to include how to set up the second axis. Once you have your data on one axis, click on the data series plotted line you want on another axis, right click, then select "format data series", then the axis tab, then select the secondary axis.

[Eli]

Yea, I figured it out, thanks.

I wish Excel allowed more than 32,000 datapoints in a series. My Tuscon to Globe to Santa Fe spreadsheet is 38k lines.

[Eli]

Hrm. I wish the time in the spreadsheet was in a standard Excel time format, then I could figure out miles.

[samwichse]

Excel 2k3 and before should allow 65535... 2k7 and up should allow considerably more (although at this point, Access is more the tool for you).

It seems to be a constant time-slice, which should make calculating distance pretty easy and likewise adding a time column. Just set a stopwatch going when you start, drive a while, then stop and divide the rows by total time

Sam

[Eli]

The spreadsheet itself allows more lines, but I mean in a chart Data Series.

Do tell about Access? Is it a better data plotter than Excel? I have a spreadsheet with 174k lines in it that I would love to plot out, even if it only means using every 10th line?

[samwichse]

It's not quite as intuitive, but it will plot out gobs of data, or allow you to say... select every other point.

I just did a graph of my temperatures db and it took 2.5 seconds to spit out a graph of max/min/average temps for 130,000 rows of data crunched into daily values.

It was able to do all 130000 points individually, but it took 85 seconds and doesn't show anything different at that scale aside from the max/min values collapsing to the same as average.