FREQUENCY function clarification

[Guest]

Can someone please clarify the difference between the two arrays you need for
a FREQUENCY function?

Thanks

 

[T. Valko]

The 2 arrays are the data_array and the bins_array.

The data_array is the array of numbers you want to analyze.

The bins_array is an array of intervals into which you want to group the
values from the data_array.

For example:

Data array.....Bins array
7...................10
40.................20
41.................30
63.................40
67.................50

FREQUENCY performs a series of counts based on the intervals of the
bins_array.

Bins array
10...counts number of values that are <=10
20...counts number of values that are >10 but <=20
30...counts number of values that are >20 but <=30
40...counts number of values that are >30 but <=40
50...counts number of values that are >40 but <=50

Now comes the confusing part. FREQUENCY adds 1 more bin than those listed in
the bins_array. This last bin is for any values that are >50. So, when you
enter the formula you need to enter it to a total number of cells that
equals the number of bins +1.

In this example we have 5 bins listed (10,20,30,40,50) so you need to select
a total of 6 cells then enter the formula as an array.

Based on this sample the results would be:

Data.....Bins.....Formula result
7..........10.......1 = 1 value is <=10
40........20.......0 = 0 values are >10 but <=20
41........30.......0 = 0 values are >20 but <=30
63........40.......1 = 1 value is >30 but <=40
67........50.......1 = 1 value is >40 but <=50
........................2 = 2 values are >50

 

[Guest]

Thanks Biff.
I’m still not sure I grasp it yet but will mess around with the function
until I get it. I am also still having some problems obtaining the numbers
that I need. Perhaps you can help?

I needed to find out the total number of stores that were listed in several
thousand rows of data. Many rows had the same store (575 possible stores). I
cannot move the data around by sorting or subtotaling. A few of weeks ago I
found this formula in the MS Discussions:
=COUNT(1/FREQUENCY($A$2:$A$8385,$A$2:$A$8385))

This seemed to work; however, I also wanted to break this total count down
by region (eight possible different regions). For this, I ended up just
assigning the arrays in groups with the exact range for each region since the
data was already sorted by region. This worked okay but I know there must be
a better way to do it.

Now I need to count the total number of employees (in total and broken down
into regions). Each employee has multiple rows so I was trying to use their
SSN. Unfortunately, I get zeroes. The SSN is in the “general†number format,
which is the same as the store and the region columns. Below is a sample of
my data (SSNs are fictitious). Advice?

SSN Store Region
523624500 00977 1
523624500 00977 1
144841174 00323 2
144841174 00323 2
292212044 08015 4
104685201 08025 4
176828434 08006 6
222707744 08006 6
234790315 00698 8
698015143 00698 8

Totals ??? 6

Region 1 Stores ??? 1
Region 2 Stores ??? 1
Region 3 Stores ??? 0
Region 4 Stores ??? 2
Region 5 Stores ??? 0
Region 6 Stores ??? 1
Region 7 Stores ??? 0
Region 8 Stores ??? 1

Thanks again!
Danni