Countif rows are within 1,000 milliseconds or 1 second

Question

I am capturing data. Some data comes in 3x/1ms (.001sec) some comes in 2x/1sec ( 1,000ms). Here is a sample of the logged data. Have 50k rows. Want to know how many times out of the 50k rows do I log 3x/1ms. The data comes in random. The red box shows data coming in 3x/1ms the blue box shows 2x per interval of second.

11:37:40.680 AM
11:37:41.140 AM
11:37:41.450 AM
11:37:41.950 AM
11:37:42.400 AM
11:37:42.700 AM
11:37:43.180 AM
11:37:43.510 AM
11:37:44.470 AM
11:37:44.780 AM
11:37:45.260 AM
11:37:45.730 AM
11:37:46.190 AM
11:37:46.660 AM
11:37:47.130 AM
11:37:47.450 AM
11:37:47.920 AM
11:37:48.390 AM
11:37:48.870 AM

Answer 1

With 2019 formulas:

I think you also can't use LET or spill formulas so I think you would have to use a helper column to identify which timestamps meet your criteria, then add up the helper column.

The helper column formula to flag each timestamp TRUE or FALSE, starting in A2:

=AND(FLOOR.MATH(A1,1/24/60/60)<>FLOOR.MATH(A2,1/24/60/60),COUNTIFS(A2:$A$22,">="&FLOOR.MATH(A2,1/24/60/60),A2:$A$22,"<"&(FLOOR.MATH(A2,1/24/60/60)+(1/24/60/60)))>=3)

FLOOR.MATH, 1/24/60/60 to get the milliseconds

COUNTIFS to count how many timestamps are above the current millisecond and below the next millisecond

Another FLOOR.MATH, 1/24/60/60 to check whether the previous timestamp is in the same millisecond as the current timestamp, so that you only count each millisecond group once

Then =COUNTIF(B2:B22,TRUE) on the helper column to count how many timestamps were flagged as TRUE matching your criteria.

Answer 2

Seems ROUND does not work with milliseconds but FLOOR.MATH does

LET to define the range you want to look at

FLOOR.MATH, 1/24/60/60 to get the milliseconds

COUNTIFS to count how many timestamps are above the current millisecond and below the next millisecond

IF to return the timestamp if there are 3 or more matches

COUNT and UNIQUE to count how many timestamps meet the "3 or more matches" criteria

Subtract 1 because UNIQUE also includes the 0 for the timestamps that do not meet the criteria.

=LET(RNG,A2:A21,MS,FLOOR.MATH(RNG,1/24/60/60),
COUNT(UNIQUE(IF(COUNTIFS(RNG,">="&MS,RNG,"<"&(MS+(1/24/60/60)))>=3,MS,0)))-1)