# determining (and displaying) slopes that exceed x

1. ## determining (and displaying) slopes that exceed x

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?

2. ## RE: determining (and displaying) slopes that exceed x

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.

----
Regards,
John Mansfield
http://www.pdbook.com

"JZip" wrote:

> 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?
>

3. ## Re: determining (and displaying) slopes that exceed x

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/
_______

JZip wrote:

> 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?
>

4. ## Re: determining (and displaying) slopes that exceed x

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

"Jon Peltier" wrote:

> 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/
> _______
>
> JZip wrote:
>
> > 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?
> >

>
>

5. ## Re: determining (and displaying) slopes that exceed x

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/
_______

JZip wrote:

> 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
>
> "Jon Peltier" wrote:
>
>
>>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/
>>_______
>>
>>JZip wrote:
>>
>>
>>>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?
>>>

>>
>>

6. ## Re: determining (and displaying) slopes that exceed x

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.

Jeff

"Jon Peltier" wrote:

> 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/
> _______
>
> JZip wrote:
>
> > 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
> >
> > "Jon Peltier" wrote:
> >
> >
> >>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/
> >>_______
> >>
> >>JZip wrote:
> >>
> >>
> >>>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?
> >>>
> >>
> >>

>
>

7. ## RE: determining (and displaying) slopes that exceed x

John,
That did the trick, thanks. Plus, it allowed me to expand my knowledge of
excel functions and their application. Thanx a ton.
Jeff

"John Mansfield" wrote:

> 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.
>
> ----
> Regards,
> John Mansfield
> http://www.pdbook.com
>
>
> "JZip" wrote:
>
> > 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?
> >

8. ## Re: determining (and displaying) slopes that exceed x

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?
Jeff

"Jon Peltier" wrote:

> 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/
> _______
>
> JZip wrote:
>
> > 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
> >
> > "Jon Peltier" wrote:
> >
> >
> >>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/
> >>_______
> >>
> >>JZip wrote:
> >>
> >>
> >>>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?
> >>>
> >>
> >>

>
>

9. ## Re: determining (and displaying) slopes that exceed x

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

"JZip" wrote:

> 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 Peltier" wrote:
>
> > 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/
> > _______
> >
> > JZip wrote:
> >
> > > 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
> > >
> > > "Jon Peltier" wrote:
> > >
> > >
> > >>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/
> > >>_______
> > >>
> > >>JZip wrote:
> > >>
> > >>
> > >>>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?
> > >>>
> > >>
> > >>

> >
> >

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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