Analyzing Data on Different Worksheets

[FarWestEng]

I have a workbook with four different worksheets containing about 10,000 data
points of flow with a time stamp on each sheet. Its too much data for me to put
into a single worksheet for analysis. I want to create a flow-duration curve
using all the data points on all worksheets. Is there a way to create a single
flow-duration curve (requires manipulating the entire data set) when the data
is spread out over 4 different worksheets?? and has so many data points.

Any help is appreciated.

Roger

 

[dvt]

I have a workbook with four different worksheets containing about
10,000 data points of flow with a time stamp on each sheet. Its too
much data for me to put into a single worksheet for analysis.

Why can't you put approx. 40k data points on one worksheet? There are about
65k rows available. Is the file size too big? Or perhaps your data points
are arranged such that you need more columns than Excel offers?

I want
to create a flow-duration curve using all the data points on all
worksheets. Is there a way to create a single flow-duration curve
(requires manipulating the entire data set) when the data is spread
out over 4 different worksheets?? and has so many data points.

I don't know what a flow-duration curve is, but I think some of your
questions are answered by Excel's specifications. These are snipped from
Excel help -- I hope they show up properly:

Worksheets referred to by a chart 255
Data series in one chart 255
Data points 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


So you can't plot 40k points in one data series. I've avoided that issue in
the past by creating >1 data series, with the first series representing
points 1 to 32k, then the next series representing points 32k+1 and higher.

And you can refer to >4 work sheets in your chart.

Without knowing the meaning of a flow-duration curve, I can't help you with
specific recommendations for your chart. Sorry about that one. Post back
with a brief description, if possible.

Dave
dvt at psu dot edu

 

[Jon Peltier]

In addition to Dave's suggestions, I'd weed out some of those data
points. You'll never be able to distinguish them all anyway, so you
could set up formulas to average blocks of data, or simply take every
Nth value.

If I were trying to analyze that much data, I'd ask myself what I was
trying to learn from it. Are you looking for high and low extremes, are
you looking to see how the mean or variance changes? Depending on the
answers, I'd try different ways to decimate or condense the data. Every
Nth point, for example, or maybe hourly mean/min/max lines. Or only the
points that differ by so many sigma from the mean.

- Jon