Charts with >254 data series
Charts with >254 data series
(OP)
Hi all, my goal would be to paste numbers from a data table produced by another standalone software.
I'd like to plot a chart with at least 1000 columns (data series) and 128 rows. As far as I've seen and read, Excel can't do that, being limited to 254, or 255 data columns.
Is there a way around that? Data series are realizations of a random process, so I really need to see the Whole data cloud. Any specific plot type which serves the purpose would be all right, but I cannot find a way around this. I'm attaching an example. I'm using Excel 2013.
The example is the random process of an acceleration response spectrum (site dynamic response of a soil column). Each series (column) is a single realization, or statistically plausible representation, of the process (simply put: a single output of the Monte Carlo simulation). Here I pasted only 211 columns, I'd like to reach at lest 1000.
I'd like to plot a chart with at least 1000 columns (data series) and 128 rows. As far as I've seen and read, Excel can't do that, being limited to 254, or 255 data columns.
Is there a way around that? Data series are realizations of a random process, so I really need to see the Whole data cloud. Any specific plot type which serves the purpose would be all right, but I cannot find a way around this. I'm attaching an example. I'm using Excel 2013.
The example is the random process of an acceleration response spectrum (site dynamic response of a soil column). Each series (column) is a single realization, or statistically plausible representation, of the process (simply put: a single output of the Monte Carlo simulation). Here I pasted only 211 columns, I'd like to reach at lest 1000.
RE: Charts with >254 data series
RE: Charts with >254 data series
RE: Charts with >254 data series
OK, I reread what you wrote. In order to use the MC analysis you need a way of characterising each run. So why not plot the envelope of all the runs, and the mean, and maybe +/- 1 2 3 standard deviations. That gives you a far more manageable 9 curves on a page.
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Charts with >254 data series
http://newtonexcelbach.wordpress.com/2012/09/05/wh...
You can get around that by copying multiple blocks of the Index function, with an empty row at the bottom, but that's a pain to do, so I wrote a short VBA routine to do the work. In the attached file I have just used your sample data, but it should work up to the row limit in Excel. If you need to go past that it would be quite simple to generate 2 or more sets of XY data.
The VBA code is not protected, and includes some brief notes. Please ask if anything isn't clear.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Charts with >254 data series
A few years ago it wouldn't have been worth the trouble, but now it's no trouble at all.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Charts with >254 data series
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Charts with >254 data series
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
RE: Charts with >254 data series
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Charts with >254 data series
Thanks very much IDS, I'm going to go carefully thru all the insights and material you provided.
RE: Charts with >254 data series
I'd try to plot density of results vs time vs amplitude. That is, a 3d histogram, where the z axis is the number of points in that bin. x is time, y is amplitude as before.
Scilab usefully includes that as a built in function, hist3d, matlab and octave call it hist3. It wouldn't be hard to do in excel, but it ain't no one liner.
Depending on how many factors you have it might be worth stratifying your original plot based on factors.
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Charts with >254 data series
In order to add a bit of color to the plot, you can use a linear gradient line preset to rainbow, with the direction set to horizontal. If actually want each plot as a different color, you might be able to insert a different color gradient stop via VBA for each series.
If you want to use VBA to make identifying the series a bit easier, you can color the individual data points as described in:
http://stackoverflow.com/questions/15981802/changi...
RE: Charts with >254 data series
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
RE: Charts with >254 data series
I don't know when the limit was increased, but it would be easy to modify the macro to produce multiple columns anyway.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Charts with >254 data series
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
RE: Charts with >254 data series
From that I generated 8 data series, and I have plotted a chart showing the complete range, and another with the range selected to the maximum Y area of the original data. I don't know the application, but the zoomed in graph looks like it shows useful information that would not be available from a plot of the mean or even frequency distribution.
To re-plot just enter the number of series requited in cell C2 on Foglio 1, then press alt-F8 and run MultiColumn2n.
I'd be interested to know if it works OK in Excel 2007. The original data is too wide for earlier versions.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Charts with >254 data series
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Charts with >254 data series
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
RE: Charts with >254 data series
how about 100 columns per sheet ? and use more sheets ?
Quando Omni Flunkus Moritati
RE: Charts with >254 data series
This is the link:
http://www.oaltd.co.uk/excel/default.htm
RE: Charts with >254 data series
There is no built-in way to link XY chart axis limits to values on the spreadsheet, but the attached file provides a UDF that will do the job. For background on how it works, see:
http://newtonexcelbach.wordpress.com/2010/04/28/au...
and
http://newtonexcelbach.wordpress.com/2012/11/25/au...
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/