 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / July 2008Too Much Chart Data for Excel Col Limits
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  SignatureBernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email >I have a user who wants to generate a chart from a really large set of >data, [quoted text clipped - 5 lines] > 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. > 1) Have you recalled that there is a 32,000 limit on the size of a data > series? [quoted text clipped - 9 lines] > > 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 _______ > Didn't know 1), thanks for pointing it out. > [quoted text clipped - 30 lines] >> > 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. > Another limit is 255 series per chart. Therefore, transposing the data will > provide sufficient space for 20 years * 52 weeks. [quoted text clipped - 40 lines] > >> > 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 _______ > Well, yeah, but transposing would definitely get us into trouble with the > OTHER dimension of their data, which is part numbers -- they virtually [quoted text clipped - 60 lines] >> >> > data >> >> > source for a chart. 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. > Larry - > [quoted text clipped - 79 lines] > >> >> > data > >> >> > source for a chart. Sometimes it's hard to think outside the box, and you need a good push. (Not you specifically.) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ > 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, [quoted text clipped - 103 lines] >> >> >> > data >> >> >> > source for a chart. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.