Count the number of times a quantity of entries occur within a range

**sameg** · 08-26-2015, 12:54 PM

I am looking for some help creating a formula that I can't seem to figure out.

I have a column of entries along a distance and I would like to count the number of times there are 10 entries within a certain range (24 meters).

Thanks in advance!

**6StringJazzer** · 08-26-2015, 02:27 PM

What does a "column of entries along a distance" look like? Can you attach a file with sample data? Is this just a list of distances?

**sameg** · 08-26-2015, 03:18 PM

Yes just a list of recorded distances.

Since I can't seem to attach a file, below is an example of the data. There would be thousands of entries in a column

"Distance
(m)"
184.6
14,929.2
14,929.2
14,929.3
14,929.3
14,929.4
14,929.5
14,929.6
14,929.8
14,929.8
14,929.8
14,929.8
14,930.1
14,930.9
22,016.2
22,016.7
22,016.7
22,016.7
22,017.9
22,018.4
22,018.5
22,019.3
22,019.5
22,020.4
22,587.0

**6StringJazzer** · 08-26-2015, 08:33 PM

count the number of times there are 10 entries within a certain range (24 meters).

There are a couple of ways to interpret this. What I have done is to count the number of times that any group of 10 consecutive entries falls within 24 meters. So suppose there are 20 consecutive entries that span 24 meters. The first 10 would therefore span 24 m, the group of 10 starting with the second entry would span 24 m, and so forth. So within that group of 20, there are 10 separate groups of 10 that span 24 m.

See attached. This solution uses a "helper" column to show a flag if the given row is the 10th row in a group of 10 that is under 24 m. Then the total counts the number of flags. There may be a more elegant way to do this without the helper column but I couldn't find one within a few minutes.