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.

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


"NormD" wrote in message
...
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.

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 didnt 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 Ive not tried
it.


"Jon Peltier" wrote:

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


"NormD" wrote in message
...
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.

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

--
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.

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


"NormD" wrote in message
...
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.


"Jon Peltier" wrote:

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


"NormD" wrote in message
...
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.

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


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

--
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.