|
Overlapping Fills in XY Scatter Chart
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 |
Overlapping Fills in XY Scatter Chart
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ups.com... 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 |
Overlapping Fills in XY Scatter Chart
On May 2, 10:40?pm, wrote:
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 |
Overlapping Fills in XY Scatter Chart
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ups.com... On May 2, 10:40?pm, wrote: 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 |
Overlapping Fills in XY Scatter Chart
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 |
Overlapping Fills in XY Scatter Chart
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 we 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 we 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ups.com... 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 |
Overlapping Fills in XY Scatter Chart
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 |
Overlapping Fills in XY Scatter Chart
Glad to help...
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ups.com... 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 |
| All times are GMT +1. The time now is 12:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com