| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating Frequencies
No formulas required if you don't mind
evenly spaced bins: http://www.freefilehosting.net/download/3bd5k |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Rounding up - number data and frequencies | Excel Discussion (Misc queries) | |||
| simple frequencies | Excel Discussion (Misc queries) | |||
| graphing data of different frequencies | Charts and Charting in Excel | |||
| Frequencies in Scatter Charts | Charts and Charting in Excel | |||
| Calculating frequencies | Excel Discussion (Misc queries) | |||
Hybrid Mode
