Re: 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;FALSE}

But only the 3 numeric items are used.

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

Regards,

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

"Debugger" <Debugger@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:93D21273-8A64-446E-A7DE-307AB1F16394@xxxxxxxxxxxxxxxx
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" <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: Sticky keys
    ... Your suggestion of using the lock key does appear to work however, I can still not fully use sticky keys to achieve what we need. ... It works fine and it immediately deselect the shift key too but the command run. ... However, if you want Windows not to deselect shift, ctrl or alt key after you press Del key then press the shift, ctrl or alt key twice to lock it down. ...
    (microsoft.public.windowsxp.general)
  • Re: Sticky keys
    ... Your suggestion of using the lock key does appear to work however, I can still not fully use sticky keys to achieve what we need. ... It works fine and it immediately deselect the shift key too but the command run. ... However, if you want Windows not to deselect shift, ctrl or alt key after you press Del key then press the shift, ctrl or alt key twice to lock it down. ...
    (microsoft.public.windowsxp.general)
  • Re: Keydown/up events in userform to set shortcut keys (Revisited)
    ... I didn't thought CTRL key had a callback until I read Peter Hewett's code. ... Private Sub MyTb_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) ... Dim strKeys As String ... Public myKey, myChar As String ...
    (microsoft.public.word.vba.general)
  • Re: MIN function
    ... did you enter it with ctrl + shift & enter? ... the whole RangeL ... >>> in Verified column L where the values in Verified column A fall in the ...
    (microsoft.public.excel)
  • Re: Access 2007 multi-select list box problem
    ... tested the simple vs extended multi-select yet. ... Tips for Access users - http://allenbrowne.com/tips.html ... Shift, but when testing I don't know how it could be. ... The ctrl and shift ...
    (microsoft.public.access.forms)