+ Reply to Thread
Results 1 to 9 of 9

determining (and displaying) slopes that exceed x

  1. #1
    JZip
    Guest

    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. #2
    John Mansfield
    Guest

    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. #3
    Jon Peltier
    Guest

    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. #4
    JZip
    Guest

    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. #5
    Jon Peltier
    Guest

    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. #6
    JZip
    Guest

    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.

    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. #7
    JZip
    Guest

    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. #8
    JZip
    Guest

    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?
    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. #9
    JZip
    Guest

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

    > >
    > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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