Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Michelle Wong
 
Posts: n/a
Default 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   Report Post  
Max
 
Posts: n/a
Default

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



  #3   Report Post  
kk
 
Posts: n/a
Default

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   Report Post  
Michelle Wong
 
Posts: n/a
Default

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   Report Post  
kk
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine FREQUENCY and SUM of Associated Values MichaelC Excel Worksheet Functions 3 July 3rd 05 01:54 AM
How do I setup criteria in DSUM to sum values NOT equal (<>) Mark Rucker Excel Worksheet Functions 1 June 22nd 05 06:56 PM
how to sum highest ranking values meeting criteria within a limit? QuantumPion Excel Discussion (Misc queries) 4 June 7th 05 02:15 PM
Sum the values of one column, only if they meet certain criteria . Todd Pippin Excel Worksheet Functions 0 February 8th 05 05:37 PM
Need formula to check values of data in several cells as criteria VCTECH Excel Worksheet Functions 0 November 19th 04 10:54 PM


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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"