Remember Me?

#1
 Michelle Wong Posts: n/a
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
#2
 Max Posts: n/a

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

--
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

#3
 kk Posts: n/a

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

#4
 Michelle Wong Posts: n/a

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

#5
 kk Posts: n/a

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post MichaelC Excel Worksheet Functions 3 July 3rd 05 01:54 AM Mark Rucker Excel Worksheet Functions 1 June 22nd 05 06:56 PM QuantumPion Excel Discussion (Misc queries) 4 June 7th 05 02:15 PM Todd Pippin Excel Worksheet Functions 0 February 8th 05 05:37 PM VCTECH Excel Worksheet Functions 0 November 19th 04 10:54 PM

All times are GMT +1. The time now is 11:51 PM.