Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 361
Default What does Excel do when you go over the limit for total data point

The maximum number of total data points in a plot is 256,000. However, I
have setup plots with 1.5 million data points. Excel does not complain
about this, but I'm guessing it is still only showing 256,000 points. The
thing is, I can't figure out which 256,000 it is showing. I don't think it's
just the first 256,000 because the data is sorted and it would look abruptly
cutoff after the 256,000th point.

I'm using a scatterplot if that helps. The plot looks correct, with
gradually decreasing density towards the outer edges of the plot. But if
it's only showing 17% of the data points, is Excel intelligently filtering
the data evenly?
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 361
Default What does Excel do when you go over the limit for total data point

Nevermind. I thought I had the data sorted, but I didn't. I can clearly see
it is cutting it off now, so I will have to filter the data in my application
first.

Please delete/lock this thread.
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default What does Excel do when you go over the limit for total data point

Usually I ask what's the use of showing more points in a chart than there
are pixels. But his time I thought I'd check it out.

I filled A1:L32001 (12 columns) with data. I tried to make a series with
A1:A32001, and Excel warned me that the limit was 32000 points per series.

I made a chart using A1:H32000 (8*32000=256000), which supposedly reaches
the maximum number of points in the chart. Fine, glad I'm using Excel 2003
and not 2007, because in the latter it would take a long time to redraw.
Then I extended the data range to I32000. No warning, and the new series was
added as if there was nothing wrong. I extended the range to column L, so I
have 12 series in the chart, each with 32000 points, which exceeds the limit
by 50%.

In VBA I ran command this from the Immediate window, with the following
results:

For Each s in ActiveChart.SeriesCollection : p = p + s.Points.Count : ?
s.Name, s.Points.Count, p : Next
Series1 32000 32000
Series2 32000 64000
Series3 32000 96000
Series4 32000 128000
Series5 32000 160000
Series6 32000 192000
Series7 32000 224000
Series8 32000 256000
Series9 32000 288000
Series10 32000 320000
Series11 32000 352000
Series12 32000 384000

So Excel thinks there are 384k points and is continuing happily along. I
don't know of any feasible way to tell which points are showing and which
(if any) have been filtered out.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"Carl" wrote in message
...
The maximum number of total data points in a plot is 256,000. However, I
have setup plots with 1.5 million data points. Excel does not complain
about this, but I'm guessing it is still only showing 256,000 points. The
thing is, I can't figure out which 256,000 it is showing. I don't think
it's
just the first 256,000 because the data is sorted and it would look
abruptly
cutoff after the 256,000th point.

I'm using a scatterplot if that helps. The plot looks correct, with
gradually decreasing density towards the outer edges of the plot. But if
it's only showing 17% of the data points, is Excel intelligently filtering
the data evenly?



  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 361
Default What does Excel do when you go over the limit for total data p

Thanks for the help, Jon. I would totally agree that it's pointless to plot
more points than can be viewed on the screen, but you don't always know how
many pixels the user's monitor displays. Plus, they may want to zoom in on a
specific area.

Also, it would be much easier to let Excel figure out how to plot it all
rather than writing my own code to filter the data before giving it to Excel.
Or at least it would be if I could be sure Excel was displaying it properly.


"Jon Peltier" wrote:

Usually I ask what's the use of showing more points in a chart than there
are pixels. But his time I thought I'd check it out.

I filled A1:L32001 (12 columns) with data. I tried to make a series with
A1:A32001, and Excel warned me that the limit was 32000 points per series.

I made a chart using A1:H32000 (8*32000=256000), which supposedly reaches
the maximum number of points in the chart. Fine, glad I'm using Excel 2003
and not 2007, because in the latter it would take a long time to redraw.
Then I extended the data range to I32000. No warning, and the new series was
added as if there was nothing wrong. I extended the range to column L, so I
have 12 series in the chart, each with 32000 points, which exceeds the limit
by 50%.

In VBA I ran command this from the Immediate window, with the following
results:

For Each s in ActiveChart.SeriesCollection : p = p + s.Points.Count : ?
s.Name, s.Points.Count, p : Next
Series1 32000 32000
Series2 32000 64000
Series3 32000 96000
Series4 32000 128000
Series5 32000 160000
Series6 32000 192000
Series7 32000 224000
Series8 32000 256000
Series9 32000 288000
Series10 32000 320000
Series11 32000 352000
Series12 32000 384000

So Excel thinks there are 384k points and is continuing happily along. I
don't know of any feasible way to tell which points are showing and which
(if any) have been filtered out.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"Carl" wrote in message
...
The maximum number of total data points in a plot is 256,000. However, I
have setup plots with 1.5 million data points. Excel does not complain
about this, but I'm guessing it is still only showing 256,000 points. The
thing is, I can't figure out which 256,000 it is showing. I don't think
it's
just the first 256,000 because the data is sorted and it would look
abruptly
cutoff after the 256,000th point.

I'm using a scatterplot if that helps. The plot looks correct, with
gradually decreasing density towards the outer edges of the plot. But if
it's only showing 17% of the data points, is Excel intelligently filtering
the data evenly?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving a line chart data point revises data table value in Excel ' Ed Smith Charts and Charting in Excel 2 November 16th 12 02:03 PM
Data point on line is not over the point/tick in X axis... TomCat Charts and Charting in Excel 2 September 6th 07 01:36 PM
excel 2007, how to select a data point and cycle through data points [email protected] Charts and Charting in Excel 5 September 4th 07 12:29 PM
Excel should let you graph every n-th data point in a column warrenrob50 Charts and Charting in Excel 3 March 3rd 06 09:42 PM
Create an if-then formula in Excel to limit column total? Nancy M Excel Discussion (Misc queries) 2 February 13th 05 11:47 PM


All times are GMT +1. The time now is 05:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"