Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
JZip
 
Posts: n/a
Default 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   Report Post  
John Mansfield
 
Posts: n/a
Default

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(C3500,$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(D40,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   Report Post  
Jon Peltier
 
Posts: n/a
Default

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   Report Post  
JZip
 
Posts: n/a
Default

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   Report Post  
Jon Peltier
 
Posts: n/a
Default

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   Report Post  
JZip
 
Posts: n/a
Default

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


"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   Report Post  
JZip
 
Posts: n/a
Default

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(C3500,$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(D40,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   Report Post  
JZip
 
Posts: n/a
Default

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?





  #9   Report Post  
JZip
 
Posts: n/a
Default

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?





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 03:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"