Excel 2007 graph limits

[NormD]

Anyone know if there is a setting for changing the max number of points in a
line graph? I'm guessing the answer is "Tough!" I can't believe that 2007
increased the max rows in the table (from 64K to over 1M!) but still has a
32K limit on points in a line graph! What a waste! What the heck were they
thinking?! Heck, that's only about 8 hours of data at a 1 second interval!

Any suggestions for plotting MANY points over time?

 

[Del Cotter]

Anyone know if there is a setting for changing the max number of points in a
line graph? I'm guessing the answer is "Tough!" I can't believe that 2007
increased the max rows in the table (from 64K to over 1M!) but still has a
32K limit on points in a line graph!

I'm not familiar with the limit, so apologies if this is a silly
question, but...

Is it a limit of 32K points per chart, or only 32K points per range? If
the latter, then start a new range and format it to look just like the
first range. There'll be no way to tell the difference.

(I'm doing this more and more often these days, for various reasons, not
for the 32K reason, and I find it a useful technique for showing up
patterns. Sometimes what we need to show us a pattern is to *suppress*
information rather than highlight it. Giving the series distinctly
different format appearances just turns the graph into a confusing mess,
while turning them all to identical grey dots or identical grey lines
makes things clearer. )

Okay, I've just tried it in Excel 97, and what the error message says
is:

"The maximum number of data points you can use in a data series
for a 2-D chart is 32,000. If you want to use more than 32,000
data points, you must create two or more series."

Is there some reason why you can't do what the error message suggests?
It was the first thing I thought of.

If you go this route, try to get some value out of the exercise by
making the two series be based on some otherwise invisible difference in
the data. Are you plotting x and y over twenty four hours on a scatter
chart? See what it looks like when the "morning" x and y values are in
blue and the "afternoon" exes and wyes are in grey. (This is the
opposite of what I wrote above, turning a bland grey mess into
multicoloured dots, in the hope of seeing a morning/afternoon difference
in behaviour)

If there are still too many dots for Excel to chart, try culling the
"lazy data" out. Are there three consecutive values like this?

07/02/2007 09:52:46 10000.00001
07/02/2007 09:52:47 10000.00002
07/02/2007 09:52:48 10000.00003

Is that middle value really contributing? If not, write a formula to
lose it, or aggregate two of them into an average, and just plot the
sudden movements. Writers of programs that simulate planetary orbits
typically do this sort of thing to conserve scarce computing resources:
they concentrate their simulations on the tricky close encounter
situations and skip through the boring parts.

Or you could use the "dynamic range" techniques mentioned on this
newsgroup, that exploit named ranges and the OFFSET() function, to build
yourself a chart that looks at no more than 32000 points at once, but
that you can "pan" with a spinner form control to scroll through your
data set looking for interesting events.

 

[Jon Peltier]

I had a project a couple years back in which I had to build up several 32k
point series to reach the 256k total point maximum in a line chart. The
client couldn't envision not plotting every single point, even though (a)
there were duplicates, and (b) there was a lot of scatter. The end result is
that the chart was simply a band of points, and did not show any detail. The
client asked for a moving average, but wouldn't accept an average of every N
points which would result in 1/N plotted points in his chart. This was
hardly better. I suggested various ways of simplifying, such as plotting
statistics on an hourly basis (min, max, median, average, Nth percentiles,
blah, blah), but if every point didn't appear it was not acceptable. I
finally had to bail out several hours in the red because I couldn't talk
sense to my client.

You have to consider: What exactly is learned by plotting 32k points? 256k
points? The plot area of a typical Excel chart has only around 40k pixels.

- Jon

 

[NormD]

Thank you both for your comments. Please believe me when I say I understand
about summarizing data, plotting averages (moving and otherwise), etc. Also
please believe me when I say there are times you WANT a sea of points. I,
not Excel, should be able to make the decision. I think it was Stalin who
said "Quantity has a quality of its own." He was referring to tanks, as I
recall, not data points. Maybe it was infantry divisions.

I've not tried it, but perhaps several 32K series can follow one another so
at least the graph looks continuous. However, if one is trending that
presents a problem between series.

 

[Del Cotter]

I've not tried it, but perhaps several 32K series can follow one another so
at least the graph looks continuous. However, if one is trending that
presents a problem between series.

You could do the trending in the spreadsheet and then plot it in the
graph.

It occurred to me that intead of following one another in two continuous
ranges, you might arrange to have them interleave, say one series
showing the even hours and one showing the odd ones. The you could have
them be subtly different shades to show up the hours, and do away with
the need for vertical gridlines.

 

[NormD]

OK, you got me with this one! I understand interleave, but have never done
that in Excel. I gather there is a way to define a series with, say, with
every other point? They there would be two series, for example. I don't
know what selecting the range would be like and how Excel would handle that,
would it still complain about the total range, not recognizing the includsion
of every Nth point.

Re doing the trend line in the spreadsheet and plotting the line, my
response is "Duh!" Of course! You're absolutely right. Guess I'm too close
to the problem!

 

[Jon Peltier]

It would be ugly to try to extract alternating points directly from a long
list, but you could use formulas to get every Nth point into a particular
column (e.g., points 1,6,11,16, etc into column D and 2,7,12,17, etc into
column F; alternatively 1-20, 61-80 in column D and 21-40, 81-100 in column
F).

I didn't mean my little rant as a criticism of your approach. I understand
that it's important to have sizable data sets. Just the more trees you have,
the harder it may be to focus on the forest.

- Jon

 

[NormD]

I didn't know of an easy way to do it either, not that I know all there is to
do in Excel. I will try some contiguous series and see how that works out.

Regarding any “rant†I didn’t take it that way at all. I appreciate your
comments and suggestions.

If you have a lot of data points (e.g., several days of 1-second samples),
rolling up that info into some meaningful summaries takes some thinking and
doing. I often use high-low-close to show hourly max, min and average from
an hour of 1-second readings, resulting in three numbers instead of 3,600.
Whether just the high-low-close is an adequate representation of the
distribution of the overall data can be debated; it certainly is simpler. I
could use the 5-value high-low to include SD, for example, but I’ve not tried
it.

 

[Del Cotter]

OK, you got me with this one! I understand interleave, but have never done
that in Excel. I gather there is a way to define a series with, say, with
every other point? They there would be two series, for example. I don't
know what selecting the range would be like and how Excel would handle that,
would it still complain about the total range, not recognizing the includsion
of every Nth point.

I can think of a couple of ways I'd do it, but please don't take me too
much at my word. Alternating ranges to avoid gridlines is just the sort
of trick I'd spend time on just to see if I could do it, but it's
probably not something worth doing for a busy analyst, not when there
are conventional gridlines already there and accessible.

 

[Jon Peltier]

You could use stacked columns, one hour wide, which show min-25th
percentile-median-75th percentile-max. I showed this variation to my client
and he thought it was interesting, but still didn't show all the points.
OHLC is another decent way to display similar statistics.

- Jon

 

[Jon Peltier]

Del -

It's a rather Tufte-esque way to display the data. Use the markers to show
the data and to show the divisions in the axes. You could even apply a
checkerboard pattern, to account for horizontal gridlines as well. Of
course, too many such effects reduces the chart to something from the
circus.

- Jon