CountIf Advanced

[Praxden]

I would like to compare a column of numbers and count the onces that are within X number of numbers from each other

example

12
3
26
11
76
42

If X = 1 then the list above would return the value 1. Since only 11 and 12 are within one number of each other.


Thanks

Praxden

 

[ETID]

Is this what your lookin for?


=IF(COUNTIF(A:A,A1+$D$1)+COUNTIF(A:A,A1-$D$1)>0,"X","&quot

...fill down


where col A is where your list is and cell D1 contains the value of X

 

[Praxden]

ETID,

Thanks for responding. That doesn't seem to work the way I need. What i want to do is compare each number in the list to each other and count the numbers that are within say 5 of each other. The numbers are listed in different columns (35 total). Heres a better example.

230 I 120 I 12 I 23 I 27 I 21 ----------- Result would be 3

because 23 27 and 21 are within 5 of each other.

This analysis maynot be possible.

Thanks

Praxden

 

[tsurikov]

Hi Praxden,

The analysis is definitely possible in theory but I think this is something too complex for simple formulas like CountIf. In my book, this would call for some programming - for each number, take the difference between it and every other number, and see if this difference is within your desired range.

Good luck,
--Michael

 

[Praxden]

Thanks


Praxden