Sorry, here is the attachment

• http://files.engineering.com/getfile.aspx?folder=ba8a65be-a0ee-4d4f-94d5-5e

Do you really need to see all the individual data points? Wouldn't the overall stats be more useful (average, min, max, stdev, etc)?

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: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?

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.
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/

• http://files.engineering.com/getfile.aspx?folder=b2462a44-e7b3-453a-85af-d2

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
http://newtonexcelbach.wordpress.com/

Example of chart output attached.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

• http://files.engineering.com/getfile.aspx?folder=544aa190-0c68-40d7-9397-86

?? 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: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

It's 1000 separate sets of data plotted against the X values in column A. If you have a look at the graph provided in his attachment you'll see that just swapping rows and columns won't work.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

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.

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: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?

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:
http://stackoverflow.com/questions/15981802/changi...

There's a limit of 32000 points for any single data series.

Quote (The entire list of limitations are in the help files under Excel Specifications and Limits)

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

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

Quote:

There's a limit of 32000 points for any single data series.

Quote (Microsoft - Excel 2010)

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

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/

Yeah, sorry, Excel 2007

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

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
http://newtonexcelbach.wordpress.com/

• http://files.engineering.com/getfile.aspx?folder=982a75d9-f456-4313-a45d-d6

The previous post has just an image of the zoomed in graph attached. The spreadsheet is attached here:

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

• http://files.engineering.com/getfile.aspx?folder=89d8615a-97cb-44db-adf6-36

seems OK

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

apologies, i haven't read all the posts ...

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

Quando Omni Flunkus Moritati

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:

http://www.oaltd.co.uk/excel/default.htm

• http://files.engineering.com/getfile.aspx?folder=14a5e2ad-2090-4656-b0f0-4a

That's a very nice spreadsheet, but it's not really useful for the example in question because the X data needs to be in ascending order for the zoom and scroll to work, and also it doesn't provide any control on the Y range.

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/

• http://files.engineering.com/getfile.aspx?folder=4290ff6f-7b88-45c1-a55c-00