Challenging Kestrel Logger Data Separation and Calculation

[ryanclose]

Hi, I am having trouble finding a way to automate a large number of tedious calculations. I have a Kestrel wind speed logger set to automatically log every 30 seconds. I need to be able to take the data and report several things.

A: Total amount of time the wind was greater than 0m/s
B: Total amount of time the wind was greater than 3.57632m/s
C: Total number of times the wind exceeded and remained above 3.57632m/s for at least 5 minutes consecutively.

I have been able to calculate A: and B:, but trying to get C: automatically has been problematic. I am trying to count "gusts" that last 5 minutes or longer, and I am not interested in gusts lasting less than five minutes. Various iterations of COUNT and SUM functions are just giving me totals for the column.

Also of importance; I have a Master Summary workbook to show the results of A, B, and C for each log, as each log has a unique identifier. Is there a way to arrange the formulas so that the Master Summary workbook can pull the data from new Kestrel Logs as they are uploaded with a minimal amount of copy/paste?

Thank you,
Ryan

[leelnich]

Hi ryan, welcome! Here's an array formula* to count gusts lasting at least 5 minutes (10 measurements):

Please Login or Register  to view this content.

Named Range: Wind = 'Cycle AA MM Battery Life'!$N$12:$N$2796

The attached workbook also includes a temporary column that I added to check my results. It uses a repeating formula to do the same thing.

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

[ryanclose]

Thank you leelnich! That works perfectly! I was unaware of FREQUENCY before.

[leelnich]

You're most welcome, I enjoyed the challenge!

Also of importance; I have a Master Summary workbook to show the results of A, B, and C for each log, as each log has a unique identifier. Is there a way to arrange the formulas so that the Master Summary workbook can pull the data from new Kestrel Logs as they are uploaded with a minimal amount of copy/paste?

It sounds like you could use a macro to automate your summary process. May I suggest you start a new thread in the VBA/macros forum to get more responses. Be sure to include a sample workbook showing the form your summaries take.