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

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" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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.


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=400377