I have a user who wants to generate a chart from a really large set of data,
too much to fit within the 256 columns available in Excel 2003. (No,
transposing the data won't work either, there's too much in BOTH directions!)

Can anybody suggest a way to handle that? I thought about dumping all the
values into an array variable, but don't know a way to make that the data
source for a chart.

1) Have you recalled that there is a 32,000 limit on the size of a data
series?
2) Will the chart be readable even with 1,000 data points?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LarryP" wrote in message
...
I have a user who wants to generate a chart from a really large set of
data,
too much to fit within the 256 columns available in Excel 2003. (No,
transposing the data won't work either, there's too much in BOTH
directions!)

Can anybody suggest a way to handle that? I thought about dumping all the
values into an array variable, but don't know a way to make that the data
source for a chart.

Didn't know 1), thanks for pointing it out.

The line chart they're working with is about progress over time on a
project, with one data point for each week looking into the future. In some
instances they are using a time horizon of several years, so they need 52 * x
columns. the 256 available columns let them go out more or less five years,
but in some instances that's already proving to be insufficient. I would
think ten years/520 columns would do, but let's worst-case it at 20
years/1040 columns. (As far as rows is concerned, I haven't heard that 65535
is a problem for them, but they do sometimes use as much as 30,000 rows or
so.)

As to readability, I hear you, but that's their call. If I give them what
they want and it's ugly, that's on them.

"Bernard Liengme" wrote:

1) Have you recalled that there is a 32,000 limit on the size of a data
series?
2) Will the chart be readable even with 1,000 data points?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LarryP" wrote in message
...
I have a user who wants to generate a chart from a really large set of
data,
too much to fit within the 256 columns available in Excel 2003. (No,
transposing the data won't work either, there's too much in BOTH
directions!)

Can anybody suggest a way to handle that? I thought about dumping all the
values into an array variable, but don't know a way to make that the data
source for a chart.

Another limit is 255 series per chart. Therefore, transposing the data will
provide sufficient space for 20 years * 52 weeks.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"LarryP" wrote in message
...
Didn't know 1), thanks for pointing it out.

The line chart they're working with is about progress over time on a
project, with one data point for each week looking into the future. In
some
instances they are using a time horizon of several years, so they need 52
* x
columns. the 256 available columns let them go out more or less five
years,
but in some instances that's already proving to be insufficient. I would
think ten years/520 columns would do, but let's worst-case it at 20
years/1040 columns. (As far as rows is concerned, I haven't heard that
65535
is a problem for them, but they do sometimes use as much as 30,000 rows or
so.)

As to readability, I hear you, but that's their call. If I give them what
they want and it's ugly, that's on them.

"Bernard Liengme" wrote:

1) Have you recalled that there is a 32,000 limit on the size of a data
series?
2) Will the chart be readable even with 1,000 data points?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LarryP" wrote in message
...
I have a user who wants to generate a chart from a really large set of
data,
too much to fit within the 256 columns available in Excel 2003. (No,
transposing the data won't work either, there's too much in BOTH
directions!)

Can anybody suggest a way to handle that? I thought about dumping all
the
values into an array variable, but don't know a way to make that the
data
source for a chart.

Well, yeah, but transposing would definitely get us into trouble with the
OTHER dimension of their data, which is part numbers -- they virtually ALWAYS
have more than 256 part numbers. That's why my thoughts turned to
calculating and storing all the necessary values in a two-dimension array, if
an array can serve as a chart data source. I know vaguely that a range of
cells can be treated as an array for some purposes, so I was hoping the
reverse is also true: that an array can be treated as a range of cells
that's just too big to fit on a worksheet.

"Jon Peltier" wrote:

Another limit is 255 series per chart. Therefore, transposing the data will
provide sufficient space for 20 years * 52 weeks.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"LarryP" wrote in message
...
Didn't know 1), thanks for pointing it out.

The line chart they're working with is about progress over time on a
project, with one data point for each week looking into the future. In
some
instances they are using a time horizon of several years, so they need 52
* x
columns. the 256 available columns let them go out more or less five
years,
but in some instances that's already proving to be insufficient. I would
think ten years/520 columns would do, but let's worst-case it at 20
years/1040 columns. (As far as rows is concerned, I haven't heard that
65535
is a problem for them, but they do sometimes use as much as 30,000 rows or
so.)

As to readability, I hear you, but that's their call. If I give them what
they want and it's ugly, that's on them.

"Bernard Liengme" wrote:

1) Have you recalled that there is a 32,000 limit on the size of a data
series?
2) Will the chart be readable even with 1,000 data points?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LarryP" wrote in message
...
I have a user who wants to generate a chart from a really large set of
data,
too much to fit within the 256 columns available in Excel 2003. (No,
transposing the data won't work either, there's too much in BOTH
directions!)

Can anybody suggest a way to handle that? I thought about dumping all
the
values into an array variable, but don't know a way to make that the
data
source for a chart.

Larry -

Either one or the other items has to stop at 256, since a chart cannot
handle more than 255 series. How are you going to show even a fraction of
256 part numbers legibly? How will you handle the labeling? You shouldn't be
just building what they are asking for, you have to give them what they
need.

It sounds like they need some way to filter out most of the 30k rows (or
whatever) into a manageable subset, and maybe a set of charts, not one big
mega-chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"LarryP" wrote in message
...
Well, yeah, but transposing would definitely get us into trouble with the
OTHER dimension of their data, which is part numbers -- they virtually
ALWAYS
have more than 256 part numbers. That's why my thoughts turned to
calculating and storing all the necessary values in a two-dimension array,
if
an array can serve as a chart data source. I know vaguely that a range of
cells can be treated as an array for some purposes, so I was hoping the
reverse is also true: that an array can be treated as a range of cells
that's just too big to fit on a worksheet.

"Jon Peltier" wrote:

Another limit is 255 series per chart. Therefore, transposing the data
will
provide sufficient space for 20 years * 52 weeks.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"LarryP" wrote in message
...
Didn't know 1), thanks for pointing it out.

The line chart they're working with is about progress over time on a
project, with one data point for each week looking into the future. In
some
instances they are using a time horizon of several years, so they need
52
* x
columns. the 256 available columns let them go out more or less five
years,
but in some instances that's already proving to be insufficient. I
would
think ten years/520 columns would do, but let's worst-case it at 20
years/1040 columns. (As far as rows is concerned, I haven't heard that
65535
is a problem for them, but they do sometimes use as much as 30,000 rows
or
so.)

As to readability, I hear you, but that's their call. If I give them
what
they want and it's ugly, that's on them.

"Bernard Liengme" wrote:

1) Have you recalled that there is a 32,000 limit on the size of a
data
series?
2) Will the chart be readable even with 1,000 data points?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LarryP" wrote in message
...
I have a user who wants to generate a chart from a really large set
of
data,
too much to fit within the 256 columns available in Excel 2003.
(No,
transposing the data won't work either, there's too much in BOTH
directions!)

Can anybody suggest a way to handle that? I thought about dumping
all
the
values into an array variable, but don't know a way to make that the
data
source for a chart.