How to stop plotting empty cells in a dynamic area chart

latha2002 · #1 07-20-2009, 04:52 AM

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 · #2 07-20-2009, 06:42 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.

latha2002 · #3 07-21-2009, 05:45 AM

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 · #4 07-21-2009, 06:07 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.

latha2002 · #5 07-22-2009, 04:07 AM

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 · #6 07-22-2009, 04:09 AM

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 · #7 07-22-2009, 12:20 PM

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 · #8 07-22-2009, 01:57 PM

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 · #9 07-23-2009, 05:49 AM

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 · #10 07-23-2009, 06:06 AM

Simply change the named ranges you have defined.

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

latha2002 · #11 07-23-2009, 07:36 AM

Dear Andy,

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

Regards,
Latha

Welcome to the Excel Forum

Forum Statistics