Overlapping Fills in XY Scatter Chart

[p4alyo]

I have an XY scatter chart to which I would like to add a number of
overlapping rectangular, black and white pattern and grayscale area
fills. To make things clearer, I have posted a jpg sketch of what I am
after at:

http:/members.aol.com/p4alyo/modelchartfills.jpg

Can this be done in Excel 2000 so that any modifications to the
original curve will automatically resize the fill areas? Or, is this a
"layers" project for a graphics program because of the overlapped
(transparent) patterns needed, as well as the "pictorial" legend?

I did try shift-copying the chart from Excel into Adobe Elements, but
the quality of the copied image was awful, jaggy and fuzzy. Likewise
copying into Microsoft Image Composer was even worse. This leaves me
with another question: What is the proper way to transfer an Excel
chart to a graphics program and still retain a quality image if that
is the way to go with this problem?

Thanks for your help.
Brassman

 

[Jon Peltier]

The picture you posted wouldn't load in my browser. Whatever.

I would approach this by drawing an appropriately formatted shape where the
fills are required. Not by hand, but using code:

http://peltiertech.com/Excel/Charts/VBAdraw.html

You can take steps to make this somewhat dynamic, but I don't use it enough
for that, so I delete the old shapes and redraw them as needed.

- Jon

 

[p4alyo]

I have an XY scatter chart to which I would like to add a number of
overlapping rectangular, black and white pattern and grayscale area
fills. To make things clearer, I have posted a jpg sketch of what I am
after at:

http:/members.aol.com/p4alyo/modelchartfills.jpg

Can this be done in Excel 2000 so that any modifications to the
original curve will automatically resize the fill areas? Or, is this a
"layers" project for a graphics program because of the overlapped
(transparent) patterns needed, as well as the "pictorial" legend?

I did try shift-copying the chart from Excel into Adobe Elements, but
the quality of the copied image was awful, jaggy and fuzzy. Likewise
copying into Microsoft Image Composer was even worse. This leaves me
with another question: What is the proper way to transfer an Excel
chart to a graphics program and still retain a quality image if that
is the way to go with this problem?

Thanks for your help.
Brassman

Jon,
The link to my sketch is not an active link (I don't know how to make
it one) and needs to be copied and pasted into your browser. Since a
picture is worth a 1000 words, it will unambiguously reveal the
effects I am after and may save me from delving into VBA.

If I may, I would like to say that I am very indebted to you for
posting so many useful charting techniques on your website. I have
used your techniques for Arbitrary Gridlines and Axis Labels, Broken Y-
Axis, and Histogram tools and App references in my project. As a new
user of Excel, I would not have been able to make any headway in
charting without them. I would have had to fall back on my drafting
skills and old Leroy lettering set! It's amazing how you have extended
the limits of Excel's charting features.
Paul

 

[Jon Peltier]

Some newsreaders make the link live; Outlook Express is one of these.
Whether I click on it or paste it into my browser, I get a 'No page to
display' message.

- Jon

 

[Andy Pope]

Hi,

It maybe possible to construct the chart.
The filled areas could be made with a multi series area chart. The
biggest problem would be that area fills are not transparent. So section
R and S may not work as well as expected. The key is the order in which
you build the areas. Some areas may require multiple data series in
order to create the effect. Especially where the fill floats above the
base line.

The xy series can be combined with the area chart.

Cheers
Andy

 

[p4alyo]

Andy,
I'm happy you were able to access my posted sketch of the chart I
want. Thanks for your comments.

Yes, I see that I need to superimpose the different fill areas
starting with the largest and proceeding sequentially to the smallest
since the fills are each opaque.

Maybe I can just use another fill pattern for the places that need
overlapped fills that would otherwise require transparent layers,
since the legend could be made to indicate the simultaneous
availability of two models by stating, say, "Model C and Model D".

Since my "Date of Manufacture" axis is a value axis in this XY scatter
chart, I don't readily see how to introduce area fills. I have seen
Jon's method, which seems applicable to this problem:

http://peltiertech.com/Excel/Charts/XYAreaChart.html

However, since I need about eight separate fill series for the chart,
I am unsure whether I will obtain a Jackson Pollock-like chart after
performing the process this many times. I have not tried implementing
the method because, frankly, I don't understand the reasoning
underlying certain critical steps. Perhaps you can offer an
alternative exposition of the technique or Jon can be persuaded to add
additional details concerning the reasoning and assumptions behind the
attack on the problem and the calculation of the "area data", column
C. The use of the integer, INT, funchtion and the formula have me
befuddled. Likewise, later on in the process a secondary X axis is
established that incorporates dates!! Where did the dates come from?

Obviously the technique works. But, hating to do things by rote, the
addled brain of this member of the Woodstock Generation could benefit
from additional ellucidation.
Paul

 

[Jon Peltier]

I managed to access your image file after I added the second slash to the
address. I should have noticed that.

For overlapping areas, the use of a third color to indicate the overlap is
the approach I would take.

Since the X axis is a date axis, you don't need to go through the conversion
that has confused you in the XY Area Chart example.

I constructed the required area chart as follows. I assumed the dates were:

1/1/2006 - 1/31/2006: Model A before SN gap
1/31/2006 - 2/25/2005: Model A after SN gap
2/25/2006 - 3/5/2006: Models A & B
3/5/2006 - 4/25/2006: Model C
4/25/2006-5/20/2006: Model D
5/20/2006 - 6/5/2006: Models D & E
6/5/2006 - 6/30/2006: Model E

I assumed the serial numbers were:

0-100: Model A
100-200: Not Used
200-350: Model A
350-500: Models A & B
500-533: Model C
533-567: Model D
567-600: Models D & E
600-800: Not Used
800-900: Model E

I constructed the following range. Note that each date marking a transition
date in the chart (i.e., vertical area boundary) is required to be listed
twice. The series has two points at such a transition: the value to the left
of the transition date and the value to the right.

A - A A&B C D D&E - E
01/01/06 100 100 150 150 33 34 33 200 100
01/31/06 100 100 150 150 33 34 33 200 100
01/31/06 0 0 350 150 33 34 33 200 100
02/25/06 0 0 350 150 33 34 33 200 100
02/25/06 0 0 0 500 33 34 33 200 100
03/05/06 0 0 0 500 33 34 33 200 100
03/05/06 0 0 0 0 533 34 33 200 100
04/25/06 0 0 0 0 533 34 33 200 100
04/25/06 0 0 0 0 0 567 33 200 100
05/20/06 0 0 0 0 0 567 33 200 100
05/20/06 0 0 0 0 0 0 600 200 100
06/05/06 0 0 0 0 0 0 600 200 100
06/05/06 0 0 0 0 0 0 0 0 900
06/30/06 0 0 0 0 0 0 0 0 900

Read this with a non-proportional font like Courier. The top left cell
should be blank. Select the entire range and create a stacked area chart.
Excel should recognize the dates as dates and give you a date-scale X axis.

Copy the date and SN data for the XY series (the line moving upwards across
your chart). Select the chart, and use Edit menu > Paste Special to add the
data as a new series, with categories in the first row. Select this added
series in the chart, go to Chart menu > Chart Type, and select an XY chart,
subtype line without markers. Double click on the new XY series, and on the
Axis tab, choose Primary.

I have put my sample workbook onto my web site:

http://peltiertech.com/Sample/WidgetSN.zip

- Jon

 

[p4alyo]

BEE-UUU-TEE-FUL! That's it.

Jon,
Thanks for the link to your workbook or I don't think I would have
been able to decipher the staggered data in the forum message. I
appreciate your efforts in providing such a detailed explanation. I
can see that I need to read more about how Excel handles dates, since
in the examples I've seen, dates were handled as categories not as
discrete values. When I developed my curve I converted date to
decimals to get "values" (Aug 1875 became 1875.66). It looks like I
didn't have to do this.

On first looking at your area series cell entries, I was puzzled.
Fortunately, you used the wierd serial numbers 533 and 567, which
revealed most of the entries are differences. Then I realized that,
with a stacked chart, the difference increment is what has to be added
to the stack to get to the required serial number for the horizontal
line defining the top of the specific area.

I should be able to add cell references, and the difference function
where applicable, to the table of specific area cell values that refer
back to the cells of the "cumulative" curve. Then, if I should tweak
the curve, the areas will automatically update and resize.

I also learned something from the way you handled the cumulative
curve. I used separate series for each segment to avoid getting a
vertical black line at the ends of the blank areas. But now I have to
format each series separately. You elegantly handled all three series
as a unit with blank cells to remove the undesired line segments. Now,
any format change will affect all the series at once. Nifty!

Thanks again for being so generous with your time and knowledge.
Paul

 

[Jon Peltier]

Glad to help...

- Jon