+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,289

    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.
    Attached Files Attached Files
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,289

    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. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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
    Attached Images Attached Images
    Last edited by daddylonglegs; 02-12-2015 at 04:07 PM.
    Audere est facere

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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!?!
    Last edited by XOR LX; 02-12-2015 at 04:11 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Survey Results table lookup errors
    By smudgepost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-26-2014, 07:06 AM
  2. Frequency function omitting zeros and errors
    By ArekRos in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-04-2012, 06:11 PM
  3. [SOLVED] Unable to display mail merge results using Preview Results function
    By trandle in forum Word Formatting & General
    Replies: 1
    Last Post: 05-30-2012, 12:29 AM
  4. Searching the forum results in fatal errors
    By Rick_Stanich in forum Excel General
    Replies: 1
    Last Post: 07-01-2009, 02:33 PM
  5. Tracing Errors in Formula Results in Excel 2007
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-15-2007, 03:04 AM

Bookmarks

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