=INDEX(FREQUENCY(A$1:A$15,A1+{300,-301}),1)-1
Could you elaborate a little on how it works ?

Sure....

It will be easier to understand if I switch things around and put them in
they're logical order:

=INDEX(FREQUENCY(A$1:A$15,A1+{-301,300}),2)-1

Let's use the first number in the list for this example:

1813

The Op wants a count of the value +/-300 excluding the value itself.

A1:A15 is the array of numbers

In the Frequency function that means the bins would be

1813-301 = 1512
1813+300 = 2113

If you broke down the Frequency function you would see that it is just a
series of counts like this:

=COUNTIF(A1:A15,"<=1512")
=COUNTIF(A1:A15,"1512")-COUNTIF(A1:A15,"=2113")
=COUNTIF(A1:A15,"2113")

So the Frequency function returns the array of these counts to the Index
function:

=INDEX({12;3;0},2)-1

The result we want is equivalent to:

=COUNTIF(A1:A15,"1512")-COUNTIF(A1:A15,"=2113")

which is position 2 of the indexed array {12;3;0}

Then we subtract 1 to exclude the specific value itself.

You could get the same results using a formula like this:

=COUNTIF(A$1:A$15,""&A1-301)-COUNTIF(A$1:A$15,"="&A1+300)-1

The Index method is a little "slicker" and the average calc times* (5 calcs)
a

Index = 0.000276 sec
Countif = 0.000274 sec

* using Charles Williams' RangeTimer method

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

I first saw this technique used by Ron Coderre a few weeks ago. I plan on
"promoting" it when the situation arises and I can remember to use it.

Biff

"Max" wrote in message
...
That's a nice one, Biff !
Could you elaborate a little on how it works ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"T. Valko" wrote:
Try this:

Assume your numbrs are in the range A1:A15

Enter this formula in B1 and copy down:

=INDEX(FREQUENCY(A$1:A$15,A1+{300,-301}),1)-1

Biff