ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Calculating Frequencies (https://www.excelbanter.com/excel-discussion-misc-queries/175407-calculating-frequencies.html)

Debugger
Calculating Frequencies
 
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
Calculating Frequencies
 
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

--
Gary''s Student - gsnu200767


"Debugger" wrote:

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

Ron Coderre
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" wrote in message
...
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

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

Debugger
Calculating Frequencies
 
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

"Ron Coderre" wrote:

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" wrote in message
...
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

Debugger
Calculating Frequencies
 
Thanks for the reply Herbert. It was useful.

Thanks,
Debugger

"Herbert Seidenberg" wrote:

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

Ron Coderre
Calculating Frequencies
 
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;FA LSE}

But only the 3 numeric items are used.

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

Regards,

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

"Debugger" wrote in message
...
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

"Ron Coderre" wrote:

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" wrote in message
...
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

Debugger
Calculating Frequencies
 
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

"Ron Coderre" wrote:

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;FA LSE}

But only the 3 numeric items are used.

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

Regards,

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

"Debugger" wrote in message
...
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

"Ron Coderre" wrote:

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" wrote in message
...
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


All times are GMT +1. The time now is 08:37 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com