Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Frequency Calc (Excel2000>)

    Hi,

    As far as I can see, the frequency function is determining the amount of scores below 70, based on the bin value. Can frequency determine the amount of scores above 70?

    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Frequency Calc (Excel2000>)

    Depending on what exactly you want, you could use
    =COUNT(A2:A10)-D2
    or
    =FREQUENCY(-A2:A10,-B2)
    The difference is caused by including/excluding the bin value in the count.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Frequency Calc (Excel2000>)

    No, frequency is used to give the distributions.It returns an array. The first Item is the numbers <=70 (this is displayed). You can use:
    <pre>=INDEX(FREQUENCY(A2:A10,B2),2)</pre>


    To get the second item (>70). Though to me,
    <pre>=COUNTIF(A2:A10,">"&B2)</pre>



    is more intuitive.

    Steve

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Frequency Calc (Excel2000>)

    Thx. I didn't realise that you can inverse the number and make it negative.

    The reason for the post is just to get clarity on the dynamics of Frequency. I am using it from time to time and sometimes get thrown out with the bin array. The next example I set up from the help files and when it is set up as an array function, the bins will determine the categories of count. It is only here that it will determine a count of above a highest value. I suspect that it works because more numbers are provided to the function, and it can categorise the values then. See insert!

    Thanx for the reply Hans. I got so involved in trying to modify the frequency calc, that I missed the rather obvious of subtracting the frequency result from the count of scores... <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Frequency Calc (Excel2000>)

    Hi Steve,
    >>> =INDEX(FREQUENCY(A2:A10,B2),2)... run this by me again. What is the purpose of index here?

    You and Hans have already given me what I need...thanx a lot...but I am just querying more to get clarity of portions of your answer! Tx
    Regards,
    Rudi

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Frequency Calc (Excel2000>)

    =FREQUENCY(A2:A10,B2) results in an array of 2 values. The first is the count of the numbers <=B2. The second is the count of the numbers >B2. I used the index to get the 2nd value from the array.

    You can see the array, if you would select D2 and D3 in your original example,
    press <F2> to get into edit
    confirm with ctrl-shift-enter

    The index is grabbing this second value directly...

    Steve

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Frequency Calc (Excel2000>)

    Thanx, I understand!
    Regards,
    Rudi

Posting Permissions

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