Count Consecutive Values within Range of Values

[MrRhodes2004]

Evening,

I have temperatures 0.0°C that are collected every minute (for every minute of the year). Yes, 0.5M rows of data per year.

I am attempting to count the number of maximum consecutive hours for a particular temperature range, say 5°C ± 0.5°C (4.5 to 5.5°C). I am using a data table, DRY_WeatherV2[ExtTmp °C].

I have been trying to use combinations of frequency and if statements with arrays (CSE). I tried with IFS statement but I do not think that works with arrays.

Please Login or Register  to view this content.

However, this is lacking a great deal. First, it only looks for a single value, not the range. Secondly it only counts number of consecutive minutes. Though this is easy just to divide by 60 at the end.

I've attached a snippet of the data that only has about 8,000 rows.

Thoughts? Comments?

[mcmahobt]

Something like this? Entered as an array formula with CTRL+SHIFT+ENTER:

Please Login or Register  to view this content.