Calculating Frequencies

[Debugger]

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

 

[Gary''s Student]

In C1:
=SUMPRODUCT(--(A1:A7="a"),--(B1:B7<11))

In D1:
=SUMPRODUCT(--(A1:A7="a"),--(B1:B7<26))-C1

In E1:
=SUMPRODUCT(--(A1:A7="a"),--(B1:B7<101))-C1-D1


and then something similar for the other letter values

 

[Ron Coderre]

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)

 

[Herbert Seidenberg]

No formulas required if you don't mind
evenly spaced bins:
http://www.freefilehosting.net/download/3bd5k

 

[Debugger]

I did try this one, but it didnt work.

I evaludated the formula, one which is not behaving as expected is

IF($A$1:$A$20=$D2,$B$1:$B$20)

as soon as it is true, it returns $B$1:$B$20 rather it should return the
corresponding index. The complete B range is included in the frequencies.

I am working on MS 2007.

Thanks,
Debugger

 

[Debugger]

Thanks for the reply Herbert. It was useful.

Thanks,
Debugger

 

[Ron Coderre]

Mentioning that you have Excel 2007 is definitely
something you'd want to mention first,
however, I don't believe that version would
handle the formula I posted any differently.

When you entered the formula...did you commit it by:
Holding down the CTRL and SHIFT keys when you pressed ENTER?
(instead of just pressing ENTER)

Regarding your formula evaluation comment...
This section: IF($A$1:$A$20=$D2,$B$1:$B$20)
will return an array of 20 items:
Values for matched items
and
FALSE for non-matches (which the FREQUENCY function will ignore)

Example:
That section in the E2 formula evaluates to:
{5;9;100;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

But only the 3 numeric items are used.

Does that help?
--------------------------

Regards,

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

 

[Debugger]

Yes i have used CTRL+ SHIFT + ENTER.

I have tried to debug the formula i.e.

IF($A$1:$A$20=$D2,$B$1:$B$20)

and found it is working fine ~uptill 45K records. May be the lasrge no
records were causing the problem.

Thanks for the help. It was indeed helpfull.

Thanks,
Debugger