2D Line Charts: How to Fill The Area between Two Series ??

[monirg]

Hello;

If I have 2 series plotted on a 2D Line Chart (Date as Category X, series1 col E Values Y1, series2 col F Values Y2):
(plot empty cells row # 52 as Interpolated)

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"><COL style="WIDTH: 110px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">48</TD><TD style="TEXT-ALIGN: center">Date</TD><TD style="TEXT-ALIGN: center; COLOR: #0000ff; FONT-WEIGHT: bold">Choles</TD><TD style="TEXT-ALIGN: center; COLOR: #ff9900; FONT-WEIGHT: bold">HDL</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">49</TD><TD style="TEXT-ALIGN: right">26-Aug-02</TD><TD style="TEXT-ALIGN: center">4.59</TD><TD style="TEXT-ALIGN: center">0.77</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">50</TD><TD style="TEXT-ALIGN: right">05-Mar-04</TD><TD style="TEXT-ALIGN: center">5.50</TD><TD style="TEXT-ALIGN: center">0.91</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">51</TD><TD style="TEXT-ALIGN: right">13-Apr-05</TD><TD style="TEXT-ALIGN: center">4.85</TD><TD style="TEXT-ALIGN: center">0.90</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">52</TD><TD style="TEXT-ALIGN: right">11-Jul-05</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">53</TD><TD style="TEXT-ALIGN: right">21-Sep-06</TD><TD style="TEXT-ALIGN: center">5.54</TD><TD style="TEXT-ALIGN: center">0.89</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">54</TD><TD style="TEXT-ALIGN: right">10-Jan-08</TD><TD style="TEXT-ALIGN: center">5.99</TD><TD style="TEXT-ALIGN: center">0.90</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">55</TD><TD style="TEXT-ALIGN: right">26-Feb-09</TD><TD style="TEXT-ALIGN: center">5.39</TD><TD style="TEXT-ALIGN: center">0.88</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">56</TD><TD style="TEXT-ALIGN: right">17-Nov-09</TD><TD style="TEXT-ALIGN: center">4.60</TD><TD style="TEXT-ALIGN: center">0.80</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">57</TD><TD style="TEXT-ALIGN: right">18-May-10</TD><TD style="TEXT-ALIGN: center">5.01</TD><TD style="TEXT-ALIGN: center">1.04</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">58</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">59</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
(may try "dynamic" ranges later)

Q1: How to fill (colour) the area trapped between the two segmented lines ??

Q2: If the above two series are replaced by a constant line each across the Category X to form a band, say:
..... Y1=5.00 constant for the 1st series
..... Y2=0.85 constant for the 2nd series
Would this make the filling much easier ??

Q3: How to superimpose (on the same chart) a New Series represented by a Single Point (from the above table):
say point row # 54: (X-Category = 10-Jan-08, Y-Value = 5.99) ??
It appears that the Line Chart considers the X-Category of the single point of the New Series as point # 1 regardless of its real location in the common Category-X range.
Maybe, I should combine a XY-line to represent the New Series (single point) and hide its axes ??

Thank you kindly for any help you could offer.

Regards.

 

[monirg]

Jon Peltier, MS MVP, MrExcel MVP, has kindly provided a solution to Q3 of my OP.
Thanks again Jon.

I'm still working on:

Q1: How to fill (colour) the area trapped between the two segmented lines ??

Q2: If the above two series are replaced by a constant line each across the Category X to form a band, say:
..... Y1=5.00 constant for the 1st series
..... Y2=0.85 constant for the 2nd series
Would this make the filling much easier ??

Your help would be greatly appreciated.

Regards.

 

[crook_101]

Hi,

I use the method descibed by Jon Peltier for some stuff before and it works fine, may depend on your version of excel though...link below.

Cheers,
Ian.

http://pubs.logicalexpressions.com/pub0009/lpmarticle.asp?id=590

 

[monirg]

Ian;

http://pubs.logicalexpressions.com/p...cle.asp?id=590
Looks very promissing. Will consult.

Thank you.

 

[monirg]

Jon Peltier's article in:
http://pubs.logicalexpressions.com/p...cle.asp?id=590
works perfectly for segmented straightlines on Line Charts.
Also Jon's article in:
http://peltiertech.com/Excel/Charts/XYAreaChart.html
for XY-Area Chart Series works fine.

Both do NOT work with Smoothed Line option selected, because an area chart has no such option.

Basically, here's what to do in a point-format to colour fill the area between Line1 and Line2:
-> plot Line1
-> plot Line2
-> plot duplicate of Line1; name series as, say, dupLine1
-> add col on the w/s with values: Delta = Line2 - Line1
-> plot the line Delta
-> now we have 4 lines plotted; 2 of them identical
-> select "dupLine1" line on the chart, right-click and Chart Type::Area::Stacked Area
-> select "Delta" line on the chart, right-click and select Chart Type::Area::Stacked Area
-> click "dupLine1" area, and Format Data Series::Area:: None

Thank you Jon and Ian for your help.

Regards.