Calculating Frequencies

  • Thread starter Thread starter Debugger
  • Start date Start date
D

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
 
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
 
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)
 
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
 
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)
 
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
 
Back
Top