Excel Line Graph - Missing Data Not Within Proper X Axis

[shelby55]

Hi

I am using Excel 2002.

I have a data populating graphs that is dynamic by use of named ranges. Sometimes there is data missing in a cell of the series and the graph is starting the data series at the beginning so it's plotting wrong.

For instance, the 21 periods of data are the weeks of Jan 31 to Jun 20. For the total values series there is data for all 21 periods but for YearToDate there isn't (because YTD is calculated from April 1 forward). So there isn't data in Jan 31 to Mar 28 for YearTodate however it is plotting it as having data from Jan 31 to Apr 18 and having blanks for Apr 25 to Jun 20.

Why is it doing this and how can I force it to show data for the actual months where data exists? Thanks.

 

[GlennUK]

Put =NA() in the cells for YTD prior to Apr 1. If that doesn't work post your dynamic name definitions.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[shelby55]

Hi Glenn

Sorry but that didn't work either.

There is a worksheet in the workbook called "raw data" which gets populated by Access - it is data on a daily basis with a column for week number and week ending.

I created a worksheet for the graph itself.

The x-axis information is a named range of 'myworkbook.xls'!Weekdate in the source data of the chart which is:

=OFFSET(Graph_Data!$B$9,Graph_Data!$B$5-1,0,Graph_Data!$B$6)

The YTD data series is a named range of 'myworkbook.xls'!YTD which is:

=OFFSET(Graph_Data!$O$9,Graph_Data!$O$5-1,0,Graph_Data!$O$6)

The column in the worksheet of Graph_Data for YTD is:

=IF(C9<7,"",SUMPRODUCT((('Raw Data'!$AT$3:$AT$1090)*(('Raw Data'!$A$3:$A$1090)<=Graph_Data!B9)*(('Raw Data'!$A$3:$A$1090)>=Min_Date))))/SUMPRODUCT((('Raw Data'!$I$3:$I$1090)*(('Raw Data'!$A$3:$A$1090)<=Graph_Data!B9)*(('Raw Data'!$A$3:$A$1090)>=Min_Date)))

I needed to include a minimum date because, as described in my original post, it can only be based on Apr 1 forward. So if the selection of the user has weeks from pre-April 1 then it should show as 0.

Thanks.

 

[GlennUK]

Make the YTD series the same length as x-axis series and put the =NA() in the cells prior to Apr-1.

YTD:

=OFFSET(Graph_Data!$O$9,Graph_Data!$B$5-1,0,Graph_Data!$B$6)

at a guess.



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

ALL series source data need the same number of x data points in order for them to align properly, if your x-axis is defined as a CATEGORY axis rather than a VALUE axis.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Thanks Skip and Glenn. The problem was because the graph_data has fields for range top, range size and range bottom which factor into the named range ($AA$5). However, the one for range size =count(AA9:AA29)wasn't calculating properly because of the "#DIV" results.

So I added another column with the formula of =if(iserror(AA9),"N/A",AA9)

After doing that the range now shows all and the weeks with "N/A" show as 0 on the graph which is what I wanted.

Thanks very much!!