Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Vertical line on column chart with monthly data
I am attempting to add a vertical line to a dynamic column chart. I've
followed the steps described by Jon Peltier at http://peltiertech.com/Excel/Charts/...ertSeries.html This procedure works fine until I change the base unit on the x-axis to "Days." This causes the columns to turn into vertical lines (since each monthly observation is really for, e.g., 6/1/2007). If I leave the x-axis as monthly and then add the secondary x-axis for the vertical (xy scatter) line, the scale on the secondary x-axis goes from 1/1/1900 - 12/1/2036, which shoves my vertical line far to the right of where I need it to appear on the primary x-axis. I realize I can manually alter the min and max of the secondary x-axis to be the (numerical) equivalents of my min and max for the primary x-axis. But since this is a dynamic chart, I'd prefer to have the scaling set automatically instead of manually adjusting it every few months. Any suggestions will be greatly appreciated. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Vertical line on column chart with monthly data
If the base unit is days, you plot the XY series using the date in days.
If the base unit is months, you must count the number of months since the beginning of time (January 1900 according to Excel), This is the whole number part of the X value, and the fractional part depends on where during the month you want the vertical line. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Tyler H" wrote in message ... I am attempting to add a vertical line to a dynamic column chart. I've followed the steps described by Jon Peltier at http://peltiertech.com/Excel/Charts/...ertSeries.html This procedure works fine until I change the base unit on the x-axis to "Days." This causes the columns to turn into vertical lines (since each monthly observation is really for, e.g., 6/1/2007). If I leave the x-axis as monthly and then add the secondary x-axis for the vertical (xy scatter) line, the scale on the secondary x-axis goes from 1/1/1900 - 12/1/2036, which shoves my vertical line far to the right of where I need it to appear on the primary x-axis. I realize I can manually alter the min and max of the secondary x-axis to be the (numerical) equivalents of my min and max for the primary x-axis. But since this is a dynamic chart, I'd prefer to have the scaling set automatically instead of manually adjusting it every few months. Any suggestions will be greatly appreciated. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Vertical line on column chart with monthly data
I discovered that changing the type of x-axis under Chart Options to Category
instead of Time-Scale gets around the problem of inserting a vertical line on a column chart with monthly time-series data. And instead of using dates for the vertical line x-values, indicate the number of columns to the left of the vertical line + 0.5. For example, I have monthly data from Jan 2003 - Dec 2006 and I want to insert a vertical line between the June 2006 and July 2006 columns. After changing the type of x-axis to Category (and making any necessary format changes), I paste the following series onto the chart: Data 30.5 0 30.5 1 I then plot this series on the secondary y-axis with range 0-1 and change the type of chart for the series to XY (Scatter) with data points connected by lines. I use 30.5 for the x-value because I want the line to appear between the 30th and 31st months/columns. This procedure should work as long as you're using data with regular time intervals and no missing data points have to be imputed. "Tyler H" wrote: I am attempting to add a vertical line to a dynamic column chart. I've followed the steps described by Jon Peltier at http://peltiertech.com/Excel/Charts/...ertSeries.html This procedure works fine until I change the base unit on the x-axis to "Days." This causes the columns to turn into vertical lines (since each monthly observation is really for, e.g., 6/1/2007). If I leave the x-axis as monthly and then add the secondary x-axis for the vertical (xy scatter) line, the scale on the secondary x-axis goes from 1/1/1900 - 12/1/2036, which shoves my vertical line far to the right of where I need it to appear on the primary x-axis. I realize I can manually alter the min and max of the secondary x-axis to be the (numerical) equivalents of my min and max for the primary x-axis. But since this is a dynamic chart, I'd prefer to have the scaling set automatically instead of manually adjusting it every few months. Any suggestions will be greatly appreciated. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Vertical line on column chart with monthly data
Tyler
I guess the same Excel workbook as I refered to here (http://www.microsoft.com/office/comm...6741c8ef8f&p=1) can be of use. Follow the instructions I put there. Wigi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add a Vertical Line to a Column or Line Chart with two axes already in use? | Charts and Charting in Excel | |||
Adding a vertical line to a column chart | Excel Discussion (Misc queries) | |||
Vertical line in a histogram (vertical bar chart) | Excel Discussion (Misc queries) | |||
vertical line chart | Charts and Charting in Excel | |||
How to add a vertical line to a Line Chart that aleady has 2 Y axi | Charts and Charting in Excel |