If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Thanks Jon,
I followed you all the way to the last step....then tripped How did you get the average to appear in the secondary y-axis? Do you take the value from the calculated field (in your little average table) or did you "cheat" and just type it in the title of the y-axis? How can you get that label to appear right at the height of the line. The only way I could get a label there was to type in it, and it's displayed in the middle of the y-axis, not right at the average. Bob "Jon Peltier" wrote in message ... 1. Double click on the columns, and on the Options tab, set Gap Width to zero. 2. Horizontal line: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Bob Richardson wrote: Nice idea Tushar - I've got the columnar chart looking pretty good. Is there a way to control the width of the bars (e.g. set them all to "n" pixels wide) while reducing the space between the columns to "y" pixels? One more request. Is there a way to draw a horizontal line on the column chart - to show the median? "Tushar Mehta" wrote in message ... Here's how I would do it. For one application see the 'Overview by Day' page of the newsgroup stats (http://www.tushar- mehta.com/excel/ngstats/overview-daily.html) Suppose the months are in column A and the y-values in column B as in: 1-Jan 6 1-Feb 8 1-Mar 11 1-Apr 10 1-May 13 1-Jun 11 1-Jul 9 1-Aug 16 1-Sep 12 1-Oct 14 1-Nov 12 1-Dec 19 1-Jan 17 1-Feb 21 1-Mar 19 1-Apr 20 1-May 20 1-Jun 24 1-Jul 28 1-Aug 24 1-Sep 21 1-Oct 22 1-Nov 25 1-Dec 25 Then, in column C (C1 specifically) enter the formula =IF(OR(MONTH(A1)=3,MONTH(A1)=11),MAX($B$1:$B$ 24),NA()) Obviously, you would adjust the above formula for your specific needs. This one 'shades' Nov.-Mar. Copy C1 as far down col. C as there is data in column B. Plot A:C as a column chart. Click the plotted series corresponding to column B. Select Chart | Chart Type... and change it to a Line chart. Double-click the plotted series corresponding to col. C. From the Patterns tab, set the border to none and the area to some light color. From the Options tab set the Gap Width to zero. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , "Bob Richardson" bobr at whidbey dot com says... I have several years of data - the x-axis is a time-scale. I'd like to have the winter months of the year shaded - to make it easier to see the seasonality in the data. Is there a way to pick a period each year (e.g. Nov. 15 - March 15) which will be shaded? |
#12
|
|||
|
|||
You may also want to check
Straight lines http://www.tushar-mehta.com/excel/ch...nes/index.html It has its advantages (and disadvantages) over the method Jon describes on his web site. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , "Bob Richardson" bobr at whidbey dot com says... Thanks Jon, I followed you all the way to the last step....then tripped How did you get the average to appear in the secondary y-axis? Do you take the value from the calculated field (in your little average table) or did you "cheat" and just type it in the title of the y-axis? How can you get that label to appear right at the height of the line. The only way I could get a label there was to type in it, and it's displayed in the middle of the y-axis, not right at the average. Bob "Jon Peltier" wrote in message ... 1. Double click on the columns, and on the Options tab, set Gap Width to zero. 2. Horizontal line: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Bob Richardson wrote: Nice idea Tushar - I've got the columnar chart looking pretty good. Is there a way to control the width of the bars (e.g. set them all to "n" pixels wide) while reducing the space between the columns to "y" pixels? One more request. Is there a way to draw a horizontal line on the column chart - to show the median? "Tushar Mehta" wrote in message ... Here's how I would do it. For one application see the 'Overview by Day' page of the newsgroup stats (http://www.tushar- mehta.com/excel/ngstats/overview-daily.html) Suppose the months are in column A and the y-values in column B as in: 1-Jan 6 1-Feb 8 1-Mar 11 1-Apr 10 1-May 13 1-Jun 11 1-Jul 9 1-Aug 16 1-Sep 12 1-Oct 14 1-Nov 12 1-Dec 19 1-Jan 17 1-Feb 21 1-Mar 19 1-Apr 20 1-May 20 1-Jun 24 1-Jul 28 1-Aug 24 1-Sep 21 1-Oct 22 1-Nov 25 1-Dec 25 Then, in column C (C1 specifically) enter the formula =IF(OR(MONTH(A1)=3,MONTH(A1)=11),MAX($B$1:$B$ 24),NA()) Obviously, you would adjust the above formula for your specific needs. This one 'shades' Nov.-Mar. Copy C1 as far down col. C as there is data in column B. Plot A:C as a column chart. Click the plotted series corresponding to column B. Select Chart | Chart Type... and change it to a Line chart. Double-click the plotted series corresponding to col. C. From the Patterns tab, set the border to none and the area to some light color. From the Options tab set the Gap Width to zero. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , "Bob Richardson" bobr at whidbey dot com says... I have several years of data - the x-axis is a time-scale. I'd like to have the winter months of the year shaded - to make it easier to see the seasonality in the data. Is there a way to pick a period each year (e.g. Nov. 15 - March 15) which will be shaded? |
#13
|
|||
|
|||
Bob -
That label is not on the secondary axis. It is a data label (Show Values) for the point plotted at the average value along the right edge of the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Bob Richardson wrote: Thanks Jon, I followed you all the way to the last step....then tripped How did you get the average to appear in the secondary y-axis? Do you take the value from the calculated field (in your little average table) or did you "cheat" and just type it in the title of the y-axis? How can you get that label to appear right at the height of the line. The only way I could get a label there was to type in it, and it's displayed in the middle of the y-axis, not right at the average. Bob "Jon Peltier" wrote in message ... 1. Double click on the columns, and on the Options tab, set Gap Width to zero. 2. Horizontal line: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Bob Richardson wrote: Nice idea Tushar - I've got the columnar chart looking pretty good. Is there a way to control the width of the bars (e.g. set them all to "n" pixels wide) while reducing the space between the columns to "y" pixels? One more request. Is there a way to draw a horizontal line on the column chart - to show the median? "Tushar Mehta" wrote in message ... Here's how I would do it. For one application see the 'Overview by Day' page of the newsgroup stats (http://www.tushar- mehta.com/excel/ngstats/overview-daily.html) Suppose the months are in column A and the y-values in column B as in: 1-Jan 6 1-Feb 8 1-Mar 11 1-Apr 10 1-May 13 1-Jun 11 1-Jul 9 1-Aug 16 1-Sep 12 1-Oct 14 1-Nov 12 1-Dec 19 1-Jan 17 1-Feb 21 1-Mar 19 1-Apr 20 1-May 20 1-Jun 24 1-Jul 28 1-Aug 24 1-Sep 21 1-Oct 22 1-Nov 25 1-Dec 25 Then, in column C (C1 specifically) enter the formula =IF(OR(MONTH(A1)=3,MONTH(A1)=11),MAX($B$1:$B $24),NA()) Obviously, you would adjust the above formula for your specific needs. This one 'shades' Nov.-Mar. Copy C1 as far down col. C as there is data in column B. Plot A:C as a column chart. Click the plotted series corresponding to column B. Select Chart | Chart Type... and change it to a Line chart. Double-click the plotted series corresponding to col. C. From the Patterns tab, set the border to none and the area to some light color. From the Options tab set the Gap Width to zero. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , "Bob Richardson" bobr at whidbey dot com says... I have several years of data - the x-axis is a time-scale. I'd like to have the winter months of the year shaded - to make it easier to see the seasonality in the data. Is there a way to pick a period each year (e.g. Nov. 15 - March 15) which will be shaded? |
#14
|
|||
|
|||
Got it It wasn't obvious to me at first to right click on the little
spot where the average bar crosses the y axis on the right side. "Jon Peltier" wrote in message ... Bob - That label is not on the secondary axis. It is a data label (Show Values) for the point plotted at the average value along the right edge of the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Bob Richardson wrote: Thanks Jon, I followed you all the way to the last step....then tripped How did you get the average to appear in the secondary y-axis? Do you take the value from the calculated field (in your little average table) or did you "cheat" and just type it in the title of the y-axis? How can you get that label to appear right at the height of the line. The only way I could get a label there was to type in it, and it's displayed in the middle of the y-axis, not right at the average. Bob "Jon Peltier" wrote in message ... 1. Double click on the columns, and on the Options tab, set Gap Width to zero. 2. Horizontal line: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Bob Richardson wrote: Nice idea Tushar - I've got the columnar chart looking pretty good. Is there a way to control the width of the bars (e.g. set them all to "n" pixels wide) while reducing the space between the columns to "y" pixels? One more request. Is there a way to draw a horizontal line on the column chart - to show the median? "Tushar Mehta" wrote in message ... Here's how I would do it. For one application see the 'Overview by Day' page of the newsgroup stats (http://www.tushar- mehta.com/excel/ngstats/overview-daily.html) Suppose the months are in column A and the y-values in column B as in: 1-Jan 6 1-Feb 8 1-Mar 11 1-Apr 10 1-May 13 1-Jun 11 1-Jul 9 1-Aug 16 1-Sep 12 1-Oct 14 1-Nov 12 1-Dec 19 1-Jan 17 1-Feb 21 1-Mar 19 1-Apr 20 1-May 20 1-Jun 24 1-Jul 28 1-Aug 24 1-Sep 21 1-Oct 22 1-Nov 25 1-Dec 25 Then, in column C (C1 specifically) enter the formula =IF(OR(MONTH(A1)=3,MONTH(A1)=11),MAX($B$1:$ B$24),NA()) Obviously, you would adjust the above formula for your specific needs. This one 'shades' Nov.-Mar. Copy C1 as far down col. C as there is data in column B. Plot A:C as a column chart. Click the plotted series corresponding to column B. Select Chart | Chart Type... and change it to a Line chart. Double-click the plotted series corresponding to col. C. From the Patterns tab, set the border to none and the area to some light color. From the Options tab set the Gap Width to zero. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , "Bob Richardson" bobr at whidbey dot com says... I have several years of data - the x-axis is a time-scale. I'd like to have the winter months of the year shaded - to make it easier to see the seasonality in the data. Is there a way to pick a period each year (e.g. Nov. 15 - March 15) which will be shaded? |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Use first record found in expression? | CASJAS | Running & Setting Up Queries | 17 | July 22nd, 2004 09:21 PM |
Outlook 2003 Terminal Server Time Zone issue | Robert Strom | Calendar | 2 | May 26th, 2004 10:50 PM |
Creating a time chart | Don Rountree | Charts and Charting | 1 | February 13th, 2004 03:11 PM |
'Closing' chart window *and* removing chart using VBA | Marc R. Bertrand | Charts and Charting | 7 | December 17th, 2003 04:30 PM |