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 a
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))),"",IN DEX(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$100B$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 wrote in message
...
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 a
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,MAT CH(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
wrote in message ...

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 a
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,MAT CH(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" wrote in message
...
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,MAT CH(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