Re: 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;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: How to calculate frequency of lottery numbers?
    ... Microsoft MVP Excel ... I downloaded the previous 100 PowerBall games ... array K1:K59. ... histogram to visually display the frequencies ...
    (microsoft.public.excel.newusers)
  • Re: How to calculate frequency of lottery numbers?
    ... following arrays: The first number of the 5 drawn balls went into array ... I then plotted the results using one of the Excel ... That's why I thought the Frequency function would be the right ... >> to some how end up with either an array of frequencies or some sort ...
    (microsoft.public.excel.newusers)
  • Re: How to calculate frequency of lottery numbers?
    ... Microsoft MVP Excel ... I downloaded the previous 100 PowerBall games ... following arrays: The first number of the 5 drawn balls went into array ... to some how end up with either an array of frequencies or some sort ...
    (microsoft.public.excel.newusers)
  • Re: How to calculate frequency of lottery numbers?
    ... If you aim to enjoy Excel, rather than make a fortune, I am sure you will get a lot out of the exercise. ... > an array of numbers from 1 to 59. ... > histogram to visually display the frequencies as ... > frequencies from the last 100 games. ...
    (microsoft.public.excel.newusers)
  • RE: Creating a complex search
    ... I have set up, on a separate page, two drop down lists of the available ... frequencies, these are in cells C6 and C8. ... I thought the best way to define "best match" would be to have excel ... "closest match" to a value as it would have to look at the differences ...
    (microsoft.public.excel.worksheet.functions)