You're confusing me!
First you say the 10 bin must be 1 then you say the 10 - 14 bin must be 0.
The FREQUENCY function performs a series of counts. This is how that count
breaks out based on your bins:
10 - =COUNTIF(rng,"<=10")
15 - =COUNTIF(rng,"10")-COUNTIF(rng,"15")
20 - =COUNTIF(rng,"15")-COUNTIF(rng,"20")
25 - =COUNTIF(rng,"20")-COUNTIF(rng,"25")
30 - =COUNTIF(rng,"25")-COUNTIF(rng,"30")
35 - =COUNTIF(rng,"30")-COUNTIF(rng,"35")
- =COUNTIF(rng,"35")
The way I am looking at is that 10 - 14 must display
a frequency of 0 since I have 13.1
13.1 is counted in bin 15.
Based on your bins what results do you expect?
--
Biff
Microsoft Excel MVP
"Mahadevan Swamy" wrote in message
ps.com...
Value 10.0 is the lower limit. The way I am looking at is that 10 - 14
must display a frequency of 0 since I have 13.1 in the array. The 15 -
19, 20 - 24, 25 - 29, 30 - 35. etc... Is there a better function to
help me meet my requirements? Thanks
Swamy
On Sep 21, 1:17 am, "T. Valko" wrote:
You have to select an array of cells equal in size to the number of bins
+ 1
then enter the formula as an array.
For example, based on your data and bins select the range I2:I8 then type
in
the formula and array enter.
For the bin of 10.0, the frequency must be 1
How do you arrive at that conclusion? The correct result for that bin is
0.
The first bin count criteria is less than or equal to. You have no values
that are <=10 so the correct result is 0.
The extra bin counts all values that are greater than the last bin.
Screencap:
http://img72.imageshack.us/img72/263/frequencywi7.jpg
--
Biff
Microsoft Excel MVP
"Mahadevan Swamy" wrote in message
oups.com...
Hi,
I am having some trouble using the frequency formula. All I want it to
do is to read an array of decimal numbers and a bin array and return a
proper frequency value. But all I get is a 0. I have used the CTRL +
SHIFT + ENTER keys and it still doesnt work. Here is the data
25.1 29.0 24.5 35.7 37.9
34.9 24.3 26.6 27.3 32.0
30.0 24.5 35.3 33.5 36.6
34.8 16.2 13.1 24.5 33.6
28.0 33.9 30.7 32.0 37.7
21.1 31.2 35.6 34.4 25.2
35.9 18.3 29.4 29.5 34.8
29.4 26.4 38.8 36.0 28.7
23.4 35.3 33.7 38.1 28.6
34.2 34.8 39.2 39.9 36.8
BIN
10.0
15.0
20.0
25.0
30.0
35.0
For the bin of 10.0, the frequency must be 1 and etc.....I had a
formula =FREQUENCY($A$2:$E$11, H$2:H$7) and it still returns me a
value of 0. Can anyone suggest a simple solution to this problem.
Thanks
Swamy