Using Frequency formula

J

jimbo

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
 
M

Mangesh Yadav

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
 
J

jimbo

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
 
M

Mangesh Yadav

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
 
N

nsv

I think it is essential to understand how the arrays work in Excel
*Input data* or observed values are located in an abitrary number o
rows and normally in only one column, but no problem in using mor
columns as well. In your example in the A column

*Bin range* in one column only. The bin range should range from th
minimum observed to the maximum observed value in steps of somethin
reasonable. In your case the range should be 0,1,2,3...25 (or if tha
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 exampl
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 th
array if you e.g. chose a shorter bin range; you wil have to rewrit
it, but you can always expand. Just select the complete new range wher
you want the frequency and press CTRL+SHIFT+ENTER

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

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