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?

NormD said:
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.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
NormD said:
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.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

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.


"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
NormD said:
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.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

On Wed, 7 Feb 2007, in microsoft.public.excel.charting,
NormD said:
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.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

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!

"Del Cotter" wrote:

On Wed, 7 Feb 2007, in microsoft.public.excel.charting,
NormD said:
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.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.