How to find average width of peak

[CWatters]

I have data collected every 10 mins that rises and falls in value (looks
a bit like a mountain range). I'm trying to find out how long the data
remains above a certain value on average.

For example suppose the data was

2 3 4 5 5 4 5 3 3 1 1 2 3 4 5 6 6 5 4 3 3 2 1 .....

and I wanted to find out how long it stays at 4 or above, on average.

The first occurance goes

4 5 5 4 5 which is 5 * 10 min slots = 50mins

The second occurance goes

4 5 6 6 5 4 which is 6 * 10 min slots = 60 mins

So the average time above 4 would be (50+60)/2 = 55 mins

Anyone see a way to write an equation or use functions to do this? I'm
not sure I'm experienced enough work with macros.

If it matters the data is in a single column and there are about 100,000
data points.

 

[CWatters]

I have data collected every 10 mins that rises and falls in value (looks
a bit like a mountain range). I'm trying to find out how long the data
remains above a certain value on average.

For example suppose the data was

2 3 4 5 5 4 5 3 3 1 1 2 3 4 5 6 6 5 4 3 3 2 1 .....

and I wanted to find out how long it stays at 4 or above, on average.

The first occurance goes

4 5 5 4 5 which is 5 * 10 min slots = 50mins

The second occurance goes

4 5 6 6 5 4 which is 6 * 10 min slots = 60 mins

So the average time above 4 would be (50+60)/2 = 55 mins

Anyone see a way to write an equation or use functions to do this? I'm
not sure I'm experienced enough work with macros.

If it matters the data is in a single column and there are about 100,000
data points.

Ok I think I have an idea..

If I add a helper column that has a 1 where the data is above 4 it will
look like

00111110000001111110000 etc

Then I can use SUBTOTAL to COUNT up the number of 1's in each group.

In this case the two sub totals would be 5 and 6

Then "all" I need to do is find the average of the sub totals.

So anyone know to average subtotals? Can you easily put subtotals in a
new column rather than under the data?

 

[CWatters]

Ok I think I have an idea..

If I add a helper column that has a 1 where the data is above 4 it will
look like

00111110000001111110000 etc

Then I can use SUBTOTAL to COUNT up the number of 1's in each group.

In this case the two sub totals would be 5 and 6

Then "all" I need to do is find the average of the sub totals.

So anyone know to average subtotals? Can you easily put subtotals in a
new column rather than under the data?

Oh dear looks like it might take several hours for excel to insert the
subtotals.