+ Reply to Thread
Results 1 to 5 of 5

Using Frequency formula

  1. #1
    jimbo
    Guest

    Using Frequency formula

    hi,
    im trying to figure out the frequency of a sample of students final marks
    and this is the formula im using '=FREQUENCY(FinalMarks,B11:B12)' but as i go
    from B11:B12 to B12:B13 it accumulates the score instead of just counting the
    frequency of B12:B13. how do i adjust the formula so it doesn't accumulate
    thanks


  2. #2
    Mangesh Yadav
    Guest

    Re: Using Frequency formula

    What is FinalMarks. I presume you have named the range. Frequency is an
    array formula, so no need to drag down. Check the help on its usage. You
    need to select the entire output area and enter the formula, and press ctrl
    shft enter

    Mangesh



    "jimbo" <jimbo@discussions.microsoft.com> wrote in message
    news:F101FDE5-A1EC-46E1-96D1-A1566D9193F1@microsoft.com...
    > hi,
    > im trying to figure out the frequency of a sample of students final marks
    > and this is the formula im using '=FREQUENCY(FinalMarks,B11:B12)' but as i

    go
    > from B11:B12 to B12:B13 it accumulates the score instead of just counting

    the
    > frequency of B12:B13. how do i adjust the formula so it doesn't accumulate
    > thanks
    >




  3. #3
    jimbo
    Guest

    Re: Using Frequency formula

    thanks Mangesh for your help but unfortunately it didn't fix my problem.
    maybe i should give you an example of what i want..
    used the formula =FREQUENCY(finalmarks,F9:F10)

    Using FREQUENCY()
    0 - this indicates that there is zero tens in the finalmarks
    2 - this indicates that there are two 11's
    3 - this indicates there are three 12's but infact there is only one 12 and
    that the frequency formula has accumulated the previous scores.. how do you
    stop this from happening?

    name range: finalmarks value range
    22 10
    25 11
    15 12
    13 13
    15 ...25
    12
    23
    20
    11
    20
    22
    15
    11


    "Mangesh Yadav" wrote:

    > What is FinalMarks. I presume you have named the range. Frequency is an
    > array formula, so no need to drag down. Check the help on its usage. You
    > need to select the entire output area and enter the formula, and press ctrl
    > shft enter
    >
    > Mangesh
    >
    >
    >
    > "jimbo" <jimbo@discussions.microsoft.com> wrote in message
    > news:F101FDE5-A1EC-46E1-96D1-A1566D9193F1@microsoft.com...
    > > hi,
    > > im trying to figure out the frequency of a sample of students final marks
    > > and this is the formula im using '=FREQUENCY(FinalMarks,B11:B12)' but as i

    > go
    > > from B11:B12 to B12:B13 it accumulates the score instead of just counting

    > the
    > > frequency of B12:B13. how do i adjust the formula so it doesn't accumulate
    > > thanks
    > >

    >
    >
    >


  4. #4
    Mangesh Yadav
    Guest

    Re: Using Frequency formula

    Range A1:A12 I have entered your data array
    Range B1:B3, I entered the bins array i.e. 11, 12, 13

    Then select range C1:C4 and enter the formula:
    =FREQUENCY(A1:A12,B1:B4)

    press control shift enter. The out put I got is
    2
    1
    1
    8

    Which means
    there are 2 entries for number 11 and less
    1 for number 12 and >11
    1 for 13 and >12
    and 8 more


    Mangesh





    "jimbo" <jimbo@discussions.microsoft.com> wrote in message
    news:4B9F8F9A-4807-43A7-B6D7-BC7DE33994D6@microsoft.com...
    > thanks Mangesh for your help but unfortunately it didn't fix my problem.
    > maybe i should give you an example of what i want..
    > used the formula =FREQUENCY(finalmarks,F9:F10)
    >
    > Using FREQUENCY()
    > 0 - this indicates that there is zero tens in the finalmarks
    > 2 - this indicates that there are two 11's
    > 3 - this indicates there are three 12's but infact there is only one 12

    and
    > that the frequency formula has accumulated the previous scores.. how do

    you
    > stop this from happening?
    >
    > name range: finalmarks value range
    > 22 10
    > 25 11
    > 15 12
    > 13 13
    > 15 ...25
    > 12
    > 23
    > 20
    > 11
    > 20
    > 22
    > 15
    > 11
    >
    >
    > "Mangesh Yadav" wrote:
    >
    > > What is FinalMarks. I presume you have named the range. Frequency is an
    > > array formula, so no need to drag down. Check the help on its usage. You
    > > need to select the entire output area and enter the formula, and press

    ctrl
    > > shft enter
    > >
    > > Mangesh
    > >
    > >
    > >
    > > "jimbo" <jimbo@discussions.microsoft.com> wrote in message
    > > news:F101FDE5-A1EC-46E1-96D1-A1566D9193F1@microsoft.com...
    > > > hi,
    > > > im trying to figure out the frequency of a sample of students final

    marks
    > > > and this is the formula im using '=FREQUENCY(FinalMarks,B11:B12)' but

    as i
    > > go
    > > > from B11:B12 to B12:B13 it accumulates the score instead of just

    counting
    > > the
    > > > frequency of B12:B13. how do i adjust the formula so it doesn't

    accumulate
    > > > thanks
    > > >

    > >
    > >
    > >




  5. #5
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 2013
    Posts
    349
    I think it is essential to understand how the arrays work in Excel
    Input data or observed values are located in an abitrary number of rows and normally in only one column, but no problem in using more columns as well. In your example in the A column

    Bin range in one column only. The bin range should range from the minimum observed to the maximum observed value in steps of something reasonable. In your case the range should be 0,1,2,3...25 (or if that gets too detailled try 0,2,4,6...26). In your example in the B column

    Frequency in the column adjecent to the bin range, in your example the C column.
    1. In cell C1 write =FREQUENCY(A1:A13,B1:B25)
    2. Mark the all the cells adjecent to the bin range, i.e. C1 to C25
    3. Press CTRL+SHIFT+ENTER

    You should note that it is not possible to reduce the length of the array if you e.g. chose a shorter bin range; you wil have to rewrite it, but you can always expand. Just select the complete new range where you want the frequency and press CTRL+SHIFT+ENTER

    Note if you choose to illustrate it with a column graph: The column on the graph indicating 2 observations with the value of 11 and the label 11 just under the center of the column actually indicates two observations with values between 10 and 11. The label 11 should rightly be at the right edge of the column, but this is not how Microsoft works.

    Another important thing: In this example your figures are pure integers, but somtimes integer-looking figures are the result of calculations and by the not-always-so-lucky combination of Microsoft and Pentium this can lead to false results. You can see e.g. the figure 12 repeated 7 times, but the analysis reveals only 6 observations between 11 and 12. This is because one of the 12's is actally 12.00000000000001 and therefore ends up in the 13-bin.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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