Calculating non-uniform cycle periods

[MrDidot]

I am seeking to analyse loading cycles on a beam.

I have half hourly data for five years, in two columns A: Date and B: Load in Kg. The load on the beam is not uniform over time and shows a cycle of load.

In Column C I have an AVERAGE function that returns the average load based in the 4 hour period (2 hours backward looking and 2 hours forward looking) if it is less than 50% of the maximum load.

The data in Column C returns sets of continuous vertical cells of data showing periods of low loading and empty cells where the load is greater than 50%. The sets can be as small as one cell (i.e. one hour) to a set of contiguous vertical cells for one week of low load.

My objective is to use Excel to analyse all the sets of contiguous cells in Column C to determine the time period from the first cell of each set to the last cell of that set and drop the result into another work sheet (pivot table) to further analyse these results.

Any help over the festive period would be greatly appreciated.

[MarvinP]

Hi MrDidot and welcome to the forum,

We need to see a sample of the spreadsheet and understand what you need as a final result to be much help, I think.

Secondly - Upgrade to the latest excel as the Pivot Table functions are much easier to use and understand.

You can attach a sample file by clicking on the "Go Advanced" below the message area and then on the Paper Clip Icon above the advanced message area.

[MrDidot]

Marvin,

Thank you for replying. I have added a file of sample data. The data is in half hourly periods rather than the hourly periods stated in the earlier post.

I am now working with Excel 2010.

Thanks

[:) Sixthsense :)]

It will be much more helpful to us to give solution, if you add the expected output also....

[MrDidot]

Sixthsense,

Thank you for replying. I have updated the file in the original post "Bean Loading Sample Data Update" to provide the expected output. I cant see how to delete previous files updloaded.

Kind regards

[MrDidot]

I find my solution in another forum and post the link for others.

http://msgroups.net/microsoft.public...guous-c/115433