Using Frequency formula

1. 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. 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. 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

> 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. 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
>
>
>
> > 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. 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.

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