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?