Hi,
I have to update an area chart on a monthly basis(starting from say Apr-05 to Feb-06) . The X axis range along row is (B1:L1). The y axis values (y1,y2,y3) are linked to another workbook from which the y values are pulled out.the range being (B2:L2),(B3:L3)and (B4:L4) along row respectively. When i update the chart automatically, it dips at Mar-06 as there are no y values entered. Since this excel is an analysis of monthly forecast, there is no data for mar-06. How do I prevent the chart from dipping? I want it to stop there and not dip down to zero as there is no value available for mar-06.
P.S. I have taken a sample chart for explanation and entered y values manually as I am not authorized to share complany data. Kindly provide solution for this sample sheet data.
Last edited by latha2002; 07-20-2009 at 06:05 AM.
No attachment. Check file size when posting.
The only way to get a sheer drop on an area chart is to use Time series x axis and double up on the drop point.
Alternatively use dynamic ranges so only months with data are plotted.
Hi,
Sorry could not attach the file due to internet connectivity issue.
I want the months to be visible on the x-axis but i do not want the graph to assume default value for an x-axis value that does not have a corresponding y axis value. Suppose i have some y valure for mar-06 and no yvalue for apr-06 the graph should end at mar-06 only.
Thanks and Regards,
Latha
Last edited by latha2002; 07-21-2009 at 07:04 AM.
Adjust your defined names to only include months with data.
Alternatively you would need to create the axis labels using formula and double up on the last period that contains data.
Dear Andy,
Thankyou for the solution. But, my client's requirement states that all the months for the entire year should be visible on the X-axis. The data is very large. Henceforth, doubling up of the last period containing data would not be feasible. Can a macro be written for the same? That would be more helpful in dealing with large set of data. The excel that i sent was just a sample one. Kindly help me in this regard.
Thank you in advance
Regards,
Latha
The only doubleing up that is required is the axis labels.
Even with a huge data set that can only be 255 cells in xl2003. Is that really a problem?
The chart has to be updated automatically as and when data is entered ; If the axis label is doubled for Mar-06 then for the next value i.e. apr-06 the graph starts from a zero value of the pevious month which will not be correct. Which is why I am requesting for an alternative solution.
Thank you,
Latha
In my example when I add values to Apr, in cells N29:N31, the month names automatically update and Apr is now doubled-up.
Of course you will need to set up the formula, as in row 28, but once setup it will sort itself out when new data is added.
Dear Andy,
I tried incorporating the formula that you suggested into my excel but I am having 2 issues: 1) The graph is not starting from zero. 2) The x axis does not display all the monthsin other words months in which no data is available are not visible.
Kindly help as I am not well versed with excel.
Thank you for your support.
Best Regards,
Latha
Simply change the named ranges you have defined.
YValue1: =OFFSET(xValue,1,0)
YValue2: =OFFSET(xValue,2,0)
YValue3: =OFFSET(xValue,3,0)
Dear Andy,
Thank you very much for your help. It works!!! I am so happy to see that
Regards,
Latha
What if the empty cells are instead in the beginning of the series? Can the method generate a vertical "jump" in the area chart?
Thanks
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks