Re: Calculating Frequencies

Using your posted data in A1:B7

Try this:
E1: 10
F1: 25
G1: 100

D2: a
D3: x
D4: c

Enter this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER
(instead of just ENTER) in...
E2: =INDEX(FREQUENCY(IF($A$1:$A$20=$D2,$B$1:$B$20),$E$1:E$1),COLUMNS($E:E))

Copy E2 and paste into E3:E4
Copy E2:E4 and paste across through Col_G

With your sample data, these values are returned:
(blank)_10____25___100
a________2_____0_____1
x________0_____0_____1
c________1_____0_____2

Note: there is an error in your posted table.
"x" and "c" have no values >10 and <=25
(Unless I misunderstood your criteria)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Debugger" <Debugger@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:799AA7CF-F6A7-4C1D-9160-01E5A949E905@xxxxxxxxxxxxxxxx
Hi,

If i am given a data

A B
-- --
a 5
a 9
a 100
x 27
c 75
c 10
c 34

Result

0-10 10-25 25-100
a 2 0 1
x 0 1 0
c 1 1 1

Basically i would like to calculate the frequencies for each item in
column
A. I cannot do one by one as there are very large no of item in column A.

Please suggest me what is the best way to do this?

Thanks,
Debugger




.

Relevant Pages

  • Re: Picking second high from a list
    ... With your posted data in A1:G1 ... This ARRAY FORMULA ... returns the 2nd highest ranked item (ignoring duplicates): ...
    (microsoft.public.excel.misc)
  • Re: Convert text to columns
    ... Excel likes to help, you'll see that excel remembers those parsing rules the ... You can put some dummy text into an empty cell ... spreadsheet excel converts test to columns for all posted data ...
    (microsoft.public.excel.misc)
  • Re: Help
    ... With your posted data in A1:B6 ... Microsoft MVP (Excel) ... I Need to Sum Company A and B Hours, I tried SumProduct, but I Can't Get ...
    (microsoft.public.excel.misc)
  • Re: vlookup and hlookup formula, a good challenge
    ... With your posted data in A1:F7 ... This regular formula: ... Microsoft MVP (Excel) ...
    (microsoft.public.excel.worksheet.functions)
Loading