Frequency of Numbers

Column J = Source Number
Column K = How many times that number appears in another database

Example:
J K
1 57
2 53
3 56
4 54
5 62
6 56
7 48
8 49
9 60


In cell P1 I want to display the first most frequent number from column J based on its frequency in column K, then in P2 the next most frequent number and so forth till I reach the 9th most frequent number in P9.

So my result in this scenario would be P1=5, P2=9, P3=1 ... P9=7

What is the formula in the P column that I need to use? I started using the formula LARGE(K1:K9,1) for the largest, LARGE(K1:K9,2) for the next and so forth. However, I was getting the result of column K and not the corresponding number in column J.

Please let me know how to fix this.

Thanks,
Shane

I put this formula in and copied it down 9 rows and it works for me

=MATCH(LARGE($K$1:$K$9,J1),$K$1:$K$9,0)

HTH

Try the following...

P1, copied down:

=RANK(K1,$K$1:$K$9)+COUNTIF($K$1:K1,K1)-1

Q1, copied down:

=INDEX($J$1:$J$9,MATCH(ROW()-ROW($Q$1)+1,$P$1:$P$9,0))

Hope this helps!

Originally Posted by shane24

Column J = Source Number
Column K = How many times that number appears in another database

Example:
J K
1 57
2 53
3 56
4 54
5 62
6 56
7 48
8 49
9 60


In cell P1 I want to display the first most frequent number from column J based on its frequency in column K, then in P2 the next most frequent number and so forth till I reach the 9th most frequent number in P9.

So my result in this scenario would be P1=5, P2=9, P3=1 ... P9=7

What is the formula in the P column that I need to use? I started using the formula LARGE(K1:K9,1) for the largest, LARGE(K1:K9,2) for the next and so forth. However, I was getting the result of column K and not the corresponding number in column J.

Please let me know how to fix this.

Thanks,
Shane

Is this what you want?
P1: =INDEX($J$1:$J$10,MATCH(LARGE($K$1:$K$10,J1),$K$1:$K$10,0))

Ola Sandstrom

The result will be
5
9
1
3
3
4
2
8
7

Thank you all for the responses. I apoplgize that it has taken me so long to get back to you all.

I used the formula that Ola Sandstrom posted and it worked ok; however, when you have 2 or more numbers that are ranked the same only one gets displayed. Referring back to my original example and Ola's result you can see that the number 3 which is ranked 56 is displayed twice. That is because the number 6 is also ranked as 56. 6 then never shows up in the result. How do we overcome this? Can you help me out on that?



My Original Example:
J K
1 57
2 53
3 56
4 54
5 62
6 56
7 48
8 49
9 60

From Ola:
The result will be
5
9
1
3
3
4
2
8
7

Try the solution I offered. It deals with such a scenario.

Hope this helps!

Originally Posted by shane24

Thank you all for the responses. I apoplgize that it has taken me so long to get back to you all.

I used the formula that Ola Sandstrom posted and it worked ok; however, when you have 2 or more numbers that are ranked the same only one gets displayed. Referring back to my original example and Ola's result you can see that the number 3 which is ranked 56 is displayed twice. That is because the number 6 is also ranked as 56. 6 then never shows up in the result. How do we overcome this? Can you help me out on that?



My Original Example:
J K
1 57
2 53
3 56
4 54
5 62
6 56
7 48
8 49
9 60

From Ola:
The result will be
5
9
1
3
3
4
2
8
7

Domenic,

Thank you very much for that. Unfortunately I am having a little problem understanding what it is you have done there. Is there anyway I could email my file to you, since I can't upload it here??

Much appreciated.

Shane

Sure! You can email me at [email protected]

Originally Posted by shane24

Domenic,

Thank you very much for that. Unfortunately I am having a little problem understanding what it is you have done there. Is there anyway I could email my file to you, since I can't upload it here??

Much appreciated.

Shane