Hi all.

I wonder if someone could offer an explanation as to the following behaviour of the FREQUENCY function, in particular where these results (seemingly randomly large integers) come from?

For example, when we evaluate:

=MAX(FREQUENCY(3,{0,2,4,6,8,10}))

with an array passed as the bins_array parameter, we return:

=MAX({0;0;1;0;0;0;0})

since, if you didn't already know, our data_array value of 3 is "binned" under the 3rd value in this array, i.e. 4; the other bins do not receive any values, and so the result for each of these is 0.

All fine. Equally, when we attempt:

=MAX(FREQUENCY(3,1/{2,4,6,8,10}))

which is:

=MAX(FREQUENCY(3,{0.5,0.25,0.166666666666667,0.125,0.1}))

we get:

=MAX({0;0;0;0;0;1})

since none of the bins passed were sufficiently large to house the value of 3, and so the "extra" bin which this function creates as default for anything larger than the last bin passed is here used to good effect.

But what happens if we try:

=MAX(FREQUENCY(3,1/{0,2,4,6,8,10}))

which is:

=MAX(FREQUENCY(3,{#DIV/0!,0.5,0.25,0.166666666666667,0.125,0.1}))

?

Strangely (at least to me), this results in a cell value of:

1,075,838,976

Even stranger, using Evaluate Formula gives the resolution as:

=MAX({4;4;3;3;960094920;1})

but then, in the final step, instead of returning the obvious:

960,094,920

from this array, gives:

1,075,838,976

as above.

And from where do the other elements in this array (the two 4s and two 3s) come?

Even more bizarre, the evaluation given above is just one of several: I have also witnessed the evaluation return both:

=MAX({0;0;0;0;960094720;1})

and:

=MAX({0;0;0;0;0;1})

though the cell result in both cases is still:

1,075,838,976

What gives? How is FREQUENCY calculated to give such odd results?

And why multiple returns when evaluated? Is there some element of randomness in the algorithm which is used to generate the results for the FREQUENCY function? How do #DIV/0! entries play a part in this?

As another, final example (you can easily generate your own), this:

=MAX(FREQUENCY(1000,1/{0,2,4,6,8,10,12}))

has one "evaluation" of:

=MAX({2883677;3014688;3014702;41;0;0;1})

and another of:

=MAX({65539;11;0;0;4;0;1})

both returning the cell value of:

1,101,593,604

Any help on this would be greatly appreciated!!

Regards