Frequency of values with Criteria

Hi!
I need some help in coming up with a formula that counts the frequency of
something that also matches a particular criteria.

I have a list of people and the duration that they took to do something:
Person Duration
Pax A 10
Pax B 3
Pax A 6
Pax C 2
Pax B 7
Pax C 1
Pax A 9

And I need to calculate the frequency of each person's Duration within the
range of
1
5
8
10

So the desired results are:
Range
Person 1 5 8 10
Pax A 0 0 1 2
Pax B 0 1 1 0
Pax C 1 1 0 0

I'm using Excel 2002. Any help is greatly appreciated!
Cheers
Michelle

One play ..

Assume the source list is in Sheet1, cols A & B
data from row2 down

> Person Duration
> Pax A 10
> Pax B 3
> Pax A 6
etc

In an empty col to the right, say col D,

Put in D2:
=IF(OR(A2="",COUNTIF($A$2:A2,A2)>1),"",ROW())

Copy D2 down to say, D100, to cover the max expected data

(Leave D1 empty)

In Sheet2
-------
With the headers in A1:E1 : Person 1 5 8 10

Put in A2:
=IF(ISERROR(SMALL(Sheet1!D:D,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL
(Sheet1!D:D,ROWS($A$1:A1)),Sheet1!D:D,0)))

Put in B2:
=IF($A2="","",SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100=B$1)))

Put in C2:
=IF($A2="","",SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100>B$1)*(
Sheet1!$B$2:$B$100<=C$1)))

Copy C2 across to E2

Then select A2:E2, copy down to E100
(cover the same range as per col D in Sheet1)

The above will return the desired results:

> Person 1 5 8 10
> Pax A 0 0 1 2
> Pax B 0 1 1 0
> Pax C 1 1 0 0

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Michelle Wong" <Michelle [email protected]> wrote in message
news:[email protected]...
> Hi!
> I need some help in coming up with a formula that counts the frequency of
> something that also matches a particular criteria.
>
> I have a list of people and the duration that they took to do something:
> Pax C 2
> Pax B 7
> Pax C 1
> Pax A 9
>
> And I need to calculate the frequency of each person's Duration within the
> range of
> 1
> 5
> 8
> 10
>
> So the desired results are:
> Range
> Person 1 5 8 10
> Pax A 0 0 1 2
> Pax B 0 1 1 0
> Pax C 1 1 0 0
>
> I'm using Excel 2002. Any help is greatly appreciated!
> Cheers
> Michelle

Hi Michelle,

I assume your data in A1: B8 and Bin array in F2:I2 (1,5,8,10)

To return the Unique Person Name:
Enter this array formula in E3:

=IF(ROWS($E$2:E2)<=SUMPRODUCT(($A$2:$A$8<>"")/COUNTIF($A$2:$A$8,$A$2:$A$8&"")),INDEX(A2:$A$8,MATCH(0,COUNTIF($E$2:E2,A2:$A$8),
0)),"")

Confirmed the formula by Ctrl + Shift + Enter and drag down.

Copy this formula,
=TRANSPOSE(FREQUENCY(IF($A$2:$A$8=$E3,$B$2:$B$8,""),$F$2:$I$2))

Select F3 till I3 and hit F2, and paste the formula by using Ctrl+V.

Confirmed the formula by Ctrl + Shift + Enter and drag down

It will return the result as needed.

Attached is the sample file.
http://savefile.com/files/8267872


Hope this help.



--
"Michelle Wong" <Michelle [email protected]>
wrote in message news:[email protected]...

Hi!
I need some help in coming up with a formula that counts the frequency of
something that also matches a particular criteria.

I have a list of people and the duration that they took to do something:
Person Duration
Pax A 10
Pax B 3
Pax A 6
Pax C 2
Pax B 7
Pax C 1
Pax A 9

And I need to calculate the frequency of each person's Duration within the
range of
1
5
8
10

So the desired results are:
Range
Person 1 5 8 10
Pax A 0 0 1 2
Pax B 0 1 1 0
Pax C 1 1 0 0

I'm using Excel 2002. Any help is greatly appreciated!
Cheers
Michelle

Hi kk

Thanks a lot for your help! It works! )

cheers
Michelle

"kk" wrote:

> Hi Michelle,
>
> I assume your data in A1: B8 and Bin array in F2:I2 (1,5,8,10)
>
> To return the Unique Person Name:
> Enter this array formula in E3:
>
> =IF(ROWS($E$2:E2)<=SUMPRODUCT(($A$2:$A$8<>"")/COUNTIF($A$2:$A$8,$A$2:$A$8&"")),INDEX(A2:$A$8,MATCH(0,COUNTIF($E$2:E2,A2:$A$8),
> 0)),"")
>
> Confirmed the formula by Ctrl + Shift + Enter and drag down.
>
> Copy this formula,
> =TRANSPOSE(FREQUENCY(IF($A$2:$A$8=$E3,$B$2:$B$8,""),$F$2:$I$2))
>
> Select F3 till I3 and hit F2, and paste the formula by using Ctrl+V.
>
> Confirmed the formula by Ctrl + Shift + Enter and drag down
>
> It will return the result as needed.
>
> Attached is the sample file.
> http://savefile.com/files/8267872
>
>

Hi Michelle

You're welcome!. Thank for the feed back.

kk


"Michelle Wong" <[email protected]> wrote in message
news:[email protected]...
Hi kk

Thanks a lot for your help! It works! )

cheers
Michelle

"kk" wrote:

> Hi Michelle,
>
> I assume your data in A1: B8 and Bin array in F2:I2 (1,5,8,10)
>
> To return the Unique Person Name:
> Enter this array formula in E3:
>
> =IF(ROWS($E$2:E2)<=SUMPRODUCT(($A$2:$A$8<>"")/COUNTIF($A$2:$A$8,$A$2:$A$8&"")),INDEX(A2:$A$8,MATCH(0,COUNTIF($E$2:E2,A2:$A$8),
> 0)),"")
>
> Confirmed the formula by Ctrl + Shift + Enter and drag down.
>
> Copy this formula,
> =TRANSPOSE(FREQUENCY(IF($A$2:$A$8=$E3,$B$2:$B$8,""),$F$2:$I$2))
>
> Select F3 till I3 and hit F2, and paste the formula by using Ctrl+V.
>
> Confirmed the formula by Ctrl + Shift + Enter and drag down
>
> It will return the result as needed.
>
> Attached is the sample file.
> http://savefile.com/files/8267872
>
>