Too Much Chart Data for Excel Col Limits

[LarryP]

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.

 

[Bernard Liengme]

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

 

[LarryP]

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.

 

[Jon Peltier]

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]

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]

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

 

[LarryP]

Okay, thanks. Terminology confusion on my part -- I saw "255 series in a
chart" and it didn't register, since I'm only dealing with two series, parts
and numbers. The actual limitation, then, is 255 data items in a series, I
take it.

In any event, I worked with them and we came up with a different approach
that gets them where they want to go. Thanks for your input, Jon.

 

[Jon Peltier]

Sometimes it's hard to think outside the box, and you need a good push. (Not
you specifically.)

- Jon