Re: FREQUENCY function clarification

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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





"T. Valko" wrote:

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



--
Biff
Microsoft Excel MVP


"Danni2004" <Danni2004@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FEB95400-F9D9-4920-AD92-C5615B9232FA@xxxxxxxxxxxxxxxx
Can someone please clarify the difference between the two arrays you need
for
a FREQUENCY function?

Thanks



.

Relevant Pages

  • Re: looking for straightforward pruned IDFT algorithm reference
    ... The first application has chronological groupings of 4-20 bins, ... FFT coefficients, the first three groups have bin indices 104-108, ... the dominant time consumption in computing the IFFT using ... FFTW is creating the large complex array with mostly zeros. ...
    (comp.dsp)
  • Re: Java access speed (database or arrays)
    ... query the database once, store the information into an array, and that ... You can cluster/farm app servers more easily than data stores, but data stores can be scaled pretty high and have awesome built-in caches. ... Something similar pertains to information simultaneity, information latency and cognitive information processing. ...
    (comp.lang.java.programmer)
  • Re: Storing large arrays in a table
    ... table in this database stores what training needs to be carried based ... array and read the array. ... This worked fine until i hit the 255 char ...
    (comp.databases.ms-access)
  • Re: BASIC programming, random# and arrays.
    ... This stores the random numberin an array, then goes back and checks the ... This picks numbers from 1-10, randomly, and stores into N ... REM GENERAL PURPOSE SHUFFLE ... 10 DIM USED$,CHECK$,SUCCESS$,SHUFFLE ...
    (comp.sys.atari.8bit)
  • Re: Interoperability with C
    ... have a procedure return a variable size array that you didn't allocate ... Then allocate an array ... with 'sufficient' buffer and the routine stores the data. ...
    (comp.lang.fortran)