Frequency Formula

[Mahadevan Swamy]

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

 

[Mike Middleton]

Swamy -

Before entering the FREQUENCY function, select a range of cells (single
column, seven rows).

The "bins" specify the inclusive upper limit of each interval.

Your data has some values greater than 35.0, so the seventh row of the
FREQUENCY function will show the number of values greater than 35.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[T. Valko]

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

 

[Mahadevan Swamy]

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

 

[T. Valko]

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?

 

[Mahadevan Swamy]

The way I am looking at is that 10 - 14 must display

a frequency of 0 since I have 13.1

Oh I am sorry if i confused you. Instead of typing 1, i typed 0. I am
going to try your solution to see if it helps me to find the frequency
correctly. Thanks for your help.

 

[Mahadevan Swamy]

Hi Biff,

I tried your solution and it was perfect all the way until for range
30-34.9 and 35 - 39.9

Here is what i did

10 -14.9 COUNTIF(rng, ">=10.0")-COUNTIF(rng, ">=14.9")
15 - 19.9 COUNTIF(rng, ">=15.0")-COUNTIF(rng, ">=19.9")
20 - 24.9 COUNTIF(rng, ">=20.0")-COUNTIF(rng, ">=24.9")
25 - 29.9 COUNTIF(rng, ">=25.0")-COUNTIF(rng, ">=29.9")
30 - 34.9 COUNTIF(rng, ">=30.0")-COUNTIF(rng, ">=34.9")
35 - 39.9 COUNTIF(rng, ">=35.0")-COUNTIF(rng, ">=39.9")

These are the values i got for those ranges:

Class Limits Frequency
10 -14.9 - 1
15 - 19.9- 2
20 - 24.9- 6
25 - 29.9- 12
30 - 34.9- 14
35 - 39.9- 13

Notice that 30 - 34.9 must return me a value of 15 and 35 - 39.9 must
return a value of 14 as i have crossed check by doing it manually.
Also the frequency must add up to 50 but adding to 48. Can you tell me
where the problem is? Thanks

Swamy

 

[T. Valko]

Your formulas are excluding the upper limit value.

COUNTIF(rng, ">=10.0")-COUNTIF(rng, ">=14.9")

The range being counted in that formula is 10 to 14.8. 14.9 is not being
counted (although there are no values of 14.9 in your table) and your next
interval starts at 15. The same thing is happening in all of the formulas.
Change them to read like this:

=COUNTIF(rng, ">=10.0")-COUNTIF(rng, ">14.9")

Or, better yet, put your range intervals in cells then refer to those cells:

...........G..........H
2.......10.........14.9
3.......15.........19.9
4.......20.........24.9
5.......25.........29.9
6.......30.........34.9
7.......35.........39.9

Then, enter this formula in I2 copied down to I7:

=COUNTIF(rng,">="&G2)-COUNTIF(rng,">"&H2)