determining (and displaying) slopes that exceed x

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created elevation profiles of state park trails that I have GPSed.
The X is distance in miles, the Y is elevation in feet, plotted on a scatter
chart (the locations of the GPS points is not uniform). I would like to show
the areas of the trail that exceed a particular slope in a different color.
I suspect I would have to create a new data series that includes just those
areas and then add it to the chart. How do I determine which areas meet the
criteria I set forth?
 
JZip,

Assuming the miles and feet data below, say you want to color any part of
the series that exceeds a slope of 500 feet. Try setting your data up as
follows:

Miles Feet Slope UL LL UL or LL >0
0 0
0.5 124 248 0 0
1.0 350 452 0 350 350
1.5 645 590 645 0 645
2.0 876 462 0 0
2.5 978 204 0 0
3.0 843 -270 0 0
3.5 721 -244 0 0
4.0 896 350 0 896 896
4.5 1,431 1,070 1,431 0 1,431
5.0 1,578 294 0 0
5.5 1,438 -280 0 0
6.0 1,548 220 0 0

Assume the Mile heading is in cell A1. The XY Chart original series is
based on Miles and Feet. The second series that returns the different color
is the UL (upper level) or LL (lower level) > 0 series.

The Slope is calculated as (Y2 –Y1) / (X2 – X1). This slope formula appears
in cell C3.

=(B3-B2)/(A3-A2)

The UL (upper limit) is a formula that returns any slope value greater than
500 feet. This upper limit formula appears in cell D3:

=IF(C3>500,$B3,0)

The LL (lower limit) is an offset formula that keys in on any upper limit
greater than zero. It says “if the upper limit in is greater than zero, go
to the Feet column and return the number one cell lower than the row
containing the upper limit valueâ€. This lower limit formula appears in cell
E3:

=IF(D4>0,B3,0)

The UL of LL > 0 column is anything in columns D or E that is greater than
zero. This is the column in which to create the second series.
 
Fernando Cinquegrani (http://www.prodomosua.it/ppage02.html) has some very creative
charting utilities on his web site, including some contour charts. It's in Italian,
but you could translate the page using babelfish.com or one of the other services.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon,
He certainly has some creative graphing ideas there. Unfortunately none ot
them relate to my problem. As for the contour graphs, they aren't necessary
since this data is from GIS maps that I have built.
Do you have any suggestions as to how to accomplish what I have in mind? I
have yet to try the other response i received here, but your techniques have
worked out so well for me in the past (especially the XY Scatter Area Chart -
thanks for that btw) that I thought you might have an idea as to how to
accompish what I have in mind.
If you like, I can send a copy of the pared down data of one of the charts.
Thx much.
Jeff
 
Jeff -

What I thought is that maybe his contour chart might be somehow deconvoluted. If
it's constructed of shapes, you might be able to measure slopes by the closeness of
the lines. How did you want to color the chart region? Fernando's convex hulls
example draws shapes on the chart which could serve this purpose. This is further
outside of the Excel charting box than I usually venture, which is why I thought of
Fernando's stuff.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon,
Actually, John's suggestion above worked. Only thing is, I tried applying
your scatter/area treatment to that series too, but was unsuccessful. I
think if I add the scatter/area treatment to this new series before removing
the seconday axes it may work.
These charts are getting a bit more complex than I had in mind. I would
like to find a way to display all the data I want, but perhaps find a simpler
method of doing it, or perhaps automating part of it, particulary since I am
hoping to do them for each trail in a State Park, and eventually, each park
in the state. Perhaps you can offer a suggestion.
The charts consist of:
series 1: elevation in feet (Y axis)/distance in miles (X axis)
series 2: scatter/area treatment of series 1
series 3: intersection points along trail
labels of series 3 using X-Y Chart Labeller
series 4: sections of trail exceeding slope of .099
series 5 (hopefully): scatter/area treatment of series 4

The points are products of GPS data that I collect as I hike the trails.
They can be anywhere from 150-1400 points, and require a good bit of work
before even the first series can be charted, therefore any streamlining would
be highly beneficial.

Thanks for your attention,
Jeff
 
John,
That did the trick, thanks. Plus, it allowed me to expand my knowledge of
excel functions and their application. Thanx a ton.
Jeff
 
Jon,
I still can't get the new area treatment (the one I did for the slope
series) to behave properly. It remains oriented to the secondary x axis
(facing upwards) even after I uncheck the category (x) axis crosses at max
value box on the secondary y axis. The original area treatment is
successfully flipped to be in line with the bottom of the chart, but the new
one still points skyward.
Any ideas?
Thx for all your help,
Jeff
 
jon,
ignore that last question. i'm a dope. i had the second area treatment
selected as a stacked area chart type. boy is my face red. all is good now.
again, if you have any ideas as to how to simplify this process they would
be warmly recieved, otherwise, thanks so much for your help.
jeff
 
Back
Top