Simplify XY Scatter into short line segments, individually colored

[desertsp]

I have an XY Scatter chart with hundreds of individual XY points. Each point is color coded (by a VBA macro) based on an adjacent column. These points represent elevations along a trail route (GPS data) and the color indicates the type of terrain (grassland, forest, etc.). See the attached sheet.

No matter how I format the XY point markers, I can't achieve a "continuous" look.


What I would like to achieve (conceptually):

1) Simplify the points into line segments. For instance, the uphill section between mile 6.2 and 7.3 could be represented by a single straight line rather than many individual points.

2) Color each line segment according to the predominant type of terrain between the start and end mile points. The line created in #1 would be colored green.


I hope that makes sense...the raw data is attached, along with a macro I currently use to color the points.


Can anyone recommend a scalable approach to this problem using Excel 2007 or 2010? I'll be reusing this for many similar datasets and plan to build a semi-automated solution.

Thanks!

[rlv]

A couple of comments:

a) I think you need expand on your definition of what you regard as a "continuous look"

b) The problem of simplifying points into line segements is determining which group of points to apply it to. You have to limit it to the relatively linear portions, or else your graph will look crappy. If it were me, I think I would try to do a running calculation of the linear corrleation coefficient (R^2 value) and only try to use a line segment when R^2 is higher than some criterea (say 0.98 or 0.99). This would be the cool/tricky programming part of the exercise.

[mdmackillop]

This simply records a change in "direction" for a reduced point chart. I've not looked at the colouring part.

[p45cal]

Is this continuous enough:?
18869
If so, change the data series to show without markers, and with a solid line instead, then tweak one or two lines in your macro to become:
End If
'apply color to point
'pt.Format.Fill.ForeColor.RGB = myColor
pt.Format.Line.ForeColor.RGB = myColor
'set a default marker size
'pt.MarkerSize = 3
Next
(you don't need the lines beginning with an apostrophe)
This might be good enough for you. If so, no need to reduce the number of points plotted but be aware that the chart is now telling fibs; originally it was showing the terrain AT the point plotted only, now it's showing the terrain for the point by colouring the line BETWEEN points as if the terrain along the whole length of the line betewen two points is that terrain, when it might not be the case. If the points are always quite close together, it may not matter.

[desertsp]

[QUOTE=p45cal;359363]Is this continuous enough:?[QUOTE]

That is exactly what I wanted to do! When I posted my question, was under the false impression that it was impossible to vary the line color within a single series, which is why I was using points. I figured I'd need to do something more complex like have many different series each representing a single straight line and colored appropriately. This is much simpler and "good enough".

I appreciate the other responses still, particularly because some of the datasets are much larger, and having a way to simplify them would be beneficial in terms of processing speed.