Hi all,
There's probably an easy solution here, but I couldn't find it. I have a time series of hourly data and I want to know how frequently the hourly values exceed a benchmark for more than X consecutive hours. For example, with the following columns:
A = date; B = hour (0-23), C = hits
I want to know how many times the number of hits (col C) exceeds 200 for more than 10 consecutive hours. I did this with a helper column (D = Consec hours > 200) where the formula in cell E3 is simply =IF(Table1[[#This Row],[Hits]]<200, 0, E2+1). This allows me to calculate max and see how many times we 200 for more than X consecutive hours, but it seems somewhat limited.
If I want to calculate average duration of hits>200 how would I do that? I can't use my helper column as is: A four hour "run" and a three hour "run" above 200 hits would yield (1+2+3+4 and 1+2+3)/7 for an average of 2.3. hours but the correct number is (4 + 3)/2, or 3.5. Any thoughts on how I can set this up to do more analysis of the "run" frequency and duration?
[sample worksheet attached]
Thanks for your help!
There's probably an easy solution here, but I couldn't find it. I have a time series of hourly data and I want to know how frequently the hourly values exceed a benchmark for more than X consecutive hours. For example, with the following columns:
A = date; B = hour (0-23), C = hits
I want to know how many times the number of hits (col C) exceeds 200 for more than 10 consecutive hours. I did this with a helper column (D = Consec hours > 200) where the formula in cell E3 is simply =IF(Table1[[#This Row],[Hits]]<200, 0, E2+1). This allows me to calculate max and see how many times we 200 for more than X consecutive hours, but it seems somewhat limited.
If I want to calculate average duration of hits>200 how would I do that? I can't use my helper column as is: A four hour "run" and a three hour "run" above 200 hits would yield (1+2+3+4 and 1+2+3)/7 for an average of 2.3. hours but the correct number is (4 + 3)/2, or 3.5. Any thoughts on how I can set this up to do more analysis of the "run" frequency and duration?
[sample worksheet attached]
Thanks for your help!
