Data set too complex for graph---Really? Too complex for Excel 2007?

[JET_Fusion]

Error: “The data range is too complex to be displayed. If a new range is selected, it will replace all of the series in the Series panel.”<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Problem: The graph is blank when the data set includes empty and non-empty data. If I reduce the data set to include cells with only non-empty value, the graph works. <o></o>
How can this possibly be, any suggestions?<o></o>
<o></o>
<o></o>
Graph Type: X-Y scatter with connected dots<o></o>
x-axis is a single column range with 300 entries, values are non-negative rational numbers or empty<o></o>
y-axis is a single column range with 300 entries, values are date (m/d/y) or empty<o></o>
<o></o>
Each column contains formulas <o></o>
Each row represents a data point, <o></o>
each data point begins with an empty value, <o></o>
each the data set grows by one or two a day<o></o>
<o></o>
********************<o></o>
What I’m doing.<o></o>
<o></o>
I have a data entry list that is update at least once a day. This data entry includes three parts (1) a date, (2) a calculated value, and (3) entered value. The date is used for both of the other data sets. Data entry requires a date, but not both of the other. As a consequence, I am using a reordering technique to align the data into consecutive rows. The graph is based upon the column with the consecutive rows. <o></o>
<o></o>
[In other words: data entry into columns A,B,C; column D to determine entry value either empty or non-empty (1,0); column E is sequential order of desired rows with corresponding empty rows adding zero to the count; column F counts only the non-empty rows, thus removing repeated count numbers; column G list of sequentially numbered columns; column H contains OFFSET() formula with embedded MATCH() that compares sequential ordered list to find desired data point for desired position and then offsets to the desired point. This formula also contains a single IF/THEN and the ISERROR() to display an empty cell with the list of non-empty cells in the column. Here’s an example of the formula.<o></o>
<o></o>
=IF(ISERROR(OFFSET($FK$7:$FK$302,MATCH(BE303,$BD$6:$BD$302,0)-1,0,1,1))=TRUE,"",OFFSET($FK$7:$FK$302,MATCH(BE303,$BD$6:$BD$302,0)-1,0,1,1))<o></o>
<o></o>
<o></o>
The result works beautifully, except for the graph. I wanted to take advantage of the fact that a X-Y Scatter graph won’t display empty cells in the graph string, so that as the data set grew so did the graph. <o></o>

---JET_Fusion

 

[thompsonrc]

I get the same error message "The data range is too complex.." and find your discovery of 'empty cells' interesting. I will check it out. My calcualations and graph work in Excel 2000 so why the problem in the newer version? and is Excel 2007 going to be fixed?

 

[RoryA]

2007 uses a completely new charting engine and there are a few bugs in it, especially if you haven't applied SP2. Your second question is a little vague, so the answer is yes and no.

 

[JET_Fusion]

I was never able to identify the exact problem, I didn't have the time back then for proper troubleshooting, but I stopped receiving the error after I reduced some of the columns (and their associated basic formulas) and stopped using a few of the Defined Names. I suspect the Defined Names were the root of the problem. None of the Defined Names were used directly in the formulas that were being graphed, they were a few steps removed.

I suggest looking at the indirect formulas, something might be off.

---JET_Fusion

 

[thompsonrc]

In reply to Jet Fusion
I did find that under Formulas there were two Defined names. Oddly enough they were the non compatible feature in 2000 for defining a print area. I guess this feature is no longer supported in 2007 nor does there seem to be any equivalent.
I deleted both print area defined names and still get the "too complex message when trying to edit source data for the graphs.
What to try next?