Hi all,

My query relates to what I think will probably involve the COUNTIFS function.

Specifically, I need to count the number of cells that fall within a range of values when those subsequent to each other are subtracted.

For example, in a column containing:
300.5
301.34
301.4
305.61
309.8
309.85

I need to count those numbers that are subsequent to one another, that when subtracted, equate to less than 0.1.

So the output for the above example would be 2 (as the difference between 301.34 and 301.4, and 309.8 and 309.85, is less than 1).

Any help would be massively appreciated!

"charlie111" wrote:
For example, in a column containing:
300.5
301.34
301.4
305.61
309.8
309.85

I need to count those numbers that are subsequent to one another,
that when subtracted, equate to less than 0.1.

So the output for the above example would be 2 (as the difference
between 301.34 and 301.4, and 309.8 and 309.85, is less than 1).

Suppose those numbers are in A1:A6, and they are in ascending order (as
above).

=SUMPRODUCT(--(A2:A6-A1:A5<0.1))

However, due to arithmetic anomalies, it would be prudent to write:

=SUMPRODUCT(--(ROUND(A2:A6-A1:A5,2)<0.1))

if the numbers are intended to be accurate to 2 decimal places.