# Results of FREQUENCY function with bins_array containing one or more #DIV/0! errors

1. ## Results of FREQUENCY function with bins_array containing one or more #DIV/0! errors

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

2. ## Re: Results of FREQUENCY function with bins_array containing one or more #DIV/0! errors

I cannot replicate what you describe 2002 or 2007. For the examples you provide that do not include the #Div/0! error, I get the same results. However, for the #Div/0! case, I get #N/A!'s across the board, and the final Max function returns N/A.

Perhaps a new "feature" of 2010? Perhaps something different in your processor/system that handles the #Div/0! differently?

I'm attaching my test spreadsheet -- see if it behaves like your examples, or if it behaves like I describe.

3. ## Re: Results of FREQUENCY function with bins_array containing one or more #DIV/0! errors

Thanks. Much appreciated input.

Then the issue appears to be lie somewhere in the fact that the array I passed as the bins_array was generated via an array constant, whereas you have passed yours as an actual range reference.

For example, if you go as if to edit your multi-cell array formula in column G in the formula bar, highlight only the part:

\$F\$4:\$F\$9

and press F9 to "evaluate" that range of values, and then recommit your formula, what do you get?

Regards

4. ## Re: Results of FREQUENCY function with bins_array containing one or more #DIV/0! errors

I get a replicate of what is in column F, so it appears to be calculating correctly.

If you look closer, you will note that I also calculated the formulas using array constants just like you presented here in K4 and K5, and those behaved exactly the same as the range reference versions. How did your machine compute K4 and K5 in my spreadsheet?

5. ## Re: Results of FREQUENCY function with bins_array containing one or more #DIV/0! errors

Interesting.

K5 for me gives a cell value of:

1079377920

and random, different intermediate evaluations to arrive at that value, as described.

Guess it's either an issue with my machine or with Excel 2010?

Regards

6. ## Re: Results of FREQUENCY function with bins_array containing one or more #DIV/0! errors

Hello XOR LX,

I got similar results in Excel 2010 initially

When I used this formula

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

Then I saw 40106372 as the result in the cell and the array showed different results, e.g. initially it alternated between

{0;0;0;0;0;1}

and sometimes something like

{579;599;619;639;659;1}

or

{0;0;0;0;900;1}

another array with very high values that I didn't record

....but it isn't consistent, every time I paste the formula in I get something different!

I just got another high number - see image attached - the 1s in the other cells are the results of the same formula

7. ## Re: Results of FREQUENCY function with bins_array containing one or more #DIV/0! errors

Thanks!

Curiouser and curiouser!

Perhaps this is a documented bug? Or perhaps, as I said, it's simply a result of some way in which the algorithm for generating returns from FREQUENCY operates, particularly in relation to this specific error value?

I tested with arrays containing e.g. #NUM!, though not extensively, but didn't seem to get the same issue:

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

for example, seemed not to exhibit this issue.

Thanks a lot for your input and confirmation. The plot thickens!

Cheers

Edit: bizarrely:

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

which results in an array containing a #DIV/0! and a #NUM! error, seems absolutely fine!?!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1