Excel 2003 Charting: Chart Data Too Complex

Question

I have written a macro in excel 2007 to log water-level readings. Once logged, it automatically charts the data for each of the 30 wells. However, when the workbook is opened in Excel 2003, the chart does not work complaining that the chart data is too complex to be displayed (works fine in 2007).

There is one series per well (each well data is logged on a separate worksheet) and has the following formula (so that it will automatically update the chart):

=IF(COUNTA('DW1'!$D:$D)-3>0,OFFSET('DW1'!$D$6,1,0,COUNTA('DW1'!$D:$D)-3), 0)

Where DW1 is the worksheet name containing the data for well DW1.

Any ideas about what is going on? I am using the if statement so that the chart doesn't throw errors if there is no data for a well.

I am thinking that the formulas together exceed the limit of the Series data. Anyway to shorten this or change the formula?

Thanks for any input.

Answer 1

In Excel 2003 and older versions, there is an unavoidable limit of 32,000 data points per series in a chart.

In order to display the chart, you need to reduce the number of data points. Either narrow the limits of the X-axis or use a filtering algorithm to remove some of the 'uninteresting' data points. You probably don't want to just decimate the data indiscriminately, as that may remove some of the highs and lows in the data series that are of interest.

Answer 2

I do not have an answer to your question, sorry, but a workaround that might be useful.

I worked on a project about 10 years ago where we were exceeding Excel's data-point limit for charts. As much as I wanted to use Excel for the task, I couldn't get it to work.

I resorted to using Gnuplot for the charts. It was a hassle to introduce another technology, but it performed very well on the task.

Answer 3

Going to run individual charts for each PZ. Pretty sure I am hitting some kind of limit with the data series formulas, not the data points (as there are really not that many).

Answer 4

Josh -

What's the series formula? What you've posted is a worksheet formula.

The 32k limit on points per series is the same in 2007 as in 97-2003.