Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations waross on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Charts with >254 data series 2

Status
Not open for further replies.
Mccoy

Mccoy

Geotechnical
Nov 9, 2000
907
0
0
IT
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.
 
Replies continue below

Recommended for you

Sort by date Sort by votes
Do you really need to see all the individual data points? Wouldn't the overall stats be more useful (average, min, max, stdev, etc)?
 
Upvote 0 Downvote
What cowski said, plus, you might try surface fitting to data like that and then viewing it as a contour plot, or any other 3D representation. Programs like Matlab, Octave Scilab or MathCAD would probably be a better choice for that than Excel.

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
 
Upvote 0 Downvote
If you were happy to just plot the points you could convert your multi-column data to a long two column range of XY points, using the Index() or Offset() functions. It should be easy to plot lines this way too, but life wasn't meant to be easy, so Microsoft have inserted a little "feature" into their XY charts so that if you have a blank anywhere in the X or Y range it converts the XY chart into a line chart. This doesn't happen with an empty cell, but if you have a formula that returns a blank (such as IF(A64<64,A64+1,"") ), it does. See the link below for more details.

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
 
Upvote 0 Downvote
On the question of presenting the data different ways, It seems to me that plotting everything is worth doing (especially when it is easy to do). You see not only the limits and the mean, but also get an impression of the density towards the limits, and any spurious data points or series will be obvious.

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
 
Upvote 0 Downvote
?? You state you only have 128 rows, so why be constrained by the notion that rows have to be horizontal? Excel is just as happy to graph by rows or by columns. That would allow you to have a million "columns" if you desire, just by transposing your notion of where data is supposed to go. AND you can now go up to 254 "rows" if desired.

TTFN
faq731-376
7ofakss

Need help writing a question or understanding a reply? forum1529
 
Upvote 0 Downvote
Thanks guys for your opinions, yes I definitively need to look at all the series since there may be outliers and local thinning out and densification of data in the X-Y region. the simple transposition operation in the Excel plot options didn't work.
Thanks very much IDS, I'm going to go carefully thru all the insights and material you provided.
 
Upvote 0 Downvote
Ignore everything I said about surface plotting that won't help, because your 'run' axis is essentially in a random order.

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
 
Upvote 0 Downvote
Without using VBA, You can copy all the data into two long columns on a new sheet using index(), floor(), mod(), row() and column(). Then plot the data as an XY plot. In order to hide the line going from the start to the end of each plot, add in three data points: One to the right, one below and one to the left of the plot area.

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:
 
Upvote 0 Downvote
There's a limit of 32000 points for any single data series.



The entire list of limitations are in the help files under Excel Specifications and Limits said:
Worksheets referred to by a chart 255
Data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in one chart 255
Data points (data points: Individual values that are plotted in a chart. Related data points make up a data series. Data points are represented by bars, columns, lines, slices, dots, and other shapes. These shapes are called data markers.) in a data series for 2-D charts 32,000
Data points in a data series for 3-D charts 4,000
Data points for all data series in one chart 256,000
Click to expand...

TTFN
faq731-376
7ofakss

Need help writing a question or understanding a reply? forum1529
 
Upvote 0 Downvote
There's a limit of 32000 points for any single data series.
Click to expand...


Microsoft - Excel 2010 said:
Charting specifications and limits

Feature - Maximum limit
Charts linked to a worksheet: Limited by available memory
Worksheets referred to by a chart: 255
Data series in one chart: 255
Data points in a data series for 2-D charts: Limited by available memory
Data points in a data series for 3-D charts: Limited by available memory
Data points for all data series in one chart: Limited by available memory
Click to expand...

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
 
Upvote 0 Downvote
I have modified my macro to allow more than one data series to be generated. As an example I just copied the supplied data down so there were 128 rows, then copied all except column A across for a total of 424 data series or 54272 data points. I also added constants to the copied data to generate 3 different sets of lines, rather than the same data 3 times.

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
 
Upvote 0 Downvote
apologies, i haven't read all the posts ...

how about 100 columns per sheet ? and use more sheets ?

Quando Omni Flunkus Moritati
 
Upvote 0 Downvote
This spreadsheet demonstrates the power arising from using defined names as the source for your charts. When charting data from a large data set (say more than a few hundred data points), it can help to be able to zoom and scroll through the data. I.e. to set a window of say 50 items and scroll through the data showing only that number of items. This example uses a chart and two scroll bars (for the zooming and scrolling), while the link from the scroll bar values to the chart display is done entirely using defined names - no code required.

This is the link:


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Tuckabag
  • Locked
  • Question
Acessing CMD shell in Excel/VBA with keystrokes and copying resultant data 1
Replies
8
Views
55
3DDave
3DDave
Lion_Engineer
  • Locked
  • Question
[Excel] Complicated Data Extraction
Replies
3
Views
15
EireChch
EireChch
vinodnx7
  • Locked
  • Question
count the same text from the file and create new list with number
Replies
19
Views
62
vinodnx7
vinodnx7
bojoka4052
  • Locked
  • Question
How to use the date I have, delete it, and replace it with next set of data? [Excel] 1
Replies
14
Views
15
bojoka4052
bojoka4052
iken
  • Locked
  • Question
Data Validation Lists Using Dynamic Name Range
Replies
6
Views
34
SkipVought
SkipVought
Back
Top