How to stop plotting empty cells in a dynamic area chart

[latha2002]

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.

[Andy Pope]

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.

[latha2002]

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

[Andy Pope]

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.

[latha2002]

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

[Andy Pope]

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?

[latha2002]

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

[Andy Pope]

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.

[latha2002]

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

[Andy Pope]

Simply change the named ranges you have defined.

YValue1: =OFFSET(xValue,1,0)
YValue2: =OFFSET(xValue,2,0)
YValue3: =OFFSET(xValue,3,0)

[latha2002]

Dear Andy,

Thank you very much for your help. It works!!! I am so happy to see that


Regards,
Latha

[trungbui]

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

[teylyn]

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.

[jasonineverett]

I have a similar problem. My data source doesn't extend to empty fields, but I my chart fills only half my plot area. There is no additional data beyond today's date. Tomorrow, the chart should get adjusted, but there should never be more than 18 bars (18 spaces on the horizontal axis). It's meant to cover 2 weeks plus 4 Fridays preceding those 2 weeks. The data is skewed, but the concept/format is correct. If I attempt to alter the area of either, they skew with each other so that the right half is always empty.

The spreadsheet is too large to attach, but I made a screen shot. The data area is shown in red.

chart.jpg

Suggestions?

[arlu1201]

Jason,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.