William Groff
New Member
- Joined
- Sep 24, 2016
- Messages
- 5
I'm trying to calculate the frequency of recurring numbers from multiple columns. I used MODE (=MODE($B$4:$B$108))) in the first row for the most frequent number, and for the 2nd most frequent I used
=INDEX(ROW(INDIRECT("B"&MIN($B$4:$B$108)&":B"&MAX($B$4:$B$108))),MATCH(LARGE(FREQUENCY($B$4:$B$108,ROW(INDIRECT("B"&MIN($B$4:$B$108)&":B"&MAX($B$4:$B$108)))),2),FREQUENCY($B$4:$B$108,ROW(INDIRECT("B"&MIN($B$4:$B$108)&":B"&MAX($B$4:$B$108)))),FALSE))
However, when I change highlighted number to '3' for the third most frequent (and so on for 4th and 5th), I end up with a repeated number. The second (and subsequent) formula was entered as an array, but after three days, I've given up trying to figure it out. I really do need help understanding what I am doing incorrectly.
Thanks in advance for any assistance you can provide.
This table is supposed to report the highest frequency occurrence for each column of the source data, in descending order (with 5th highest frequency on the bottom - rank order). The actual results are shown in each cell. As you can see, some numbers repeat.
<tbody abp="1111">
</tbody>
Table with source data:
A B C D E F G
<tbody abp="826">
</tbody><colgroup abp="1022"><col abp="1023"><col span="5" abp="1024"><col abp="1025"></colgroup>
=INDEX(ROW(INDIRECT("B"&MIN($B$4:$B$108)&":B"&MAX($B$4:$B$108))),MATCH(LARGE(FREQUENCY($B$4:$B$108,ROW(INDIRECT("B"&MIN($B$4:$B$108)&":B"&MAX($B$4:$B$108)))),2),FREQUENCY($B$4:$B$108,ROW(INDIRECT("B"&MIN($B$4:$B$108)&":B"&MAX($B$4:$B$108)))),FALSE))
However, when I change highlighted number to '3' for the third most frequent (and so on for 4th and 5th), I end up with a repeated number. The second (and subsequent) formula was entered as an array, but after three days, I've given up trying to figure it out. I really do need help understanding what I am doing incorrectly.
Thanks in advance for any assistance you can provide.
This table is supposed to report the highest frequency occurrence for each column of the source data, in descending order (with 5th highest frequency on the bottom - rank order). The actual results are shown in each cell. As you can see, some numbers repeat.
H | I | J | K | L | M | N | O |
1 | RANK | 1 | 2 | 3 | 4 | 5 | 6 |
2 | 1 | 3 | 28 | 50 | 64 | 69 | 8 |
3 | 2 | 5 | 25 | 21 | 59 | 64 | 25 |
4 | 3 | 2 | 12 | 21 | 29 | 67 | 12 |
5 | 4 | 2 | 11 | 15 | 31 | 61 | 3 |
6 | 5 | 2 | 11 | 15 | 29 | 61 | 3 |
<tbody abp="1111">
</tbody>
Table with source data:
A B C D E F G
DATE | 1 | 2 | 3 | 4 | 5 | 6 |
2-Jan-2016 | 5 | 6 | 15 | 29 | 42 | 10 |
6-Jan-2016 | 2 | 11 | 47 | 62 | 63 | 17 |
9-Jan-2016 | 16 | 19 | 32 | 34 | 57 | 13 |
13-Jan-2016 | 4 | 8 | 19 | 27 | 34 | 10 |
16-Jan-2016 | 3 | 51 | 52 | 61 | 64 | 6 |
20-Jan-2016 | 5 | 39 | 44 | 47 | 69 | 24 |
23-Jan-2016 | 22 | 32 | 34 | 40 | 69 | 19 |
27-Jan-2016 | 3 | 12 | 40 | 52 | 67 | 21 |
30-Jan-2016 | 5 | 12 | 16 | 31 | 43 | 18 |
3-Feb-2016 | 26 | 28 | 31 | 36 | 52 | 8 |
6-Feb-2016 | 4 | 13 | 31 | 36 | 52 | 8 |
10-Feb-2016 | 2 | 3 | 40 | 50 | 62 | 5 |
13-Feb-2016 | 7 | 15 | 18 | 19 | 36 | 20 |
17-Feb-2016 | 7 | 17 | 27 | 29 | 40 | 25 |
20-Feb-2016 | 11 | 12 | 15 | 16 | 54 | 25 |
24-Feb-2016 | 21 | 31 | 64 | 65 | 67 | 5 |
27-Feb-2016 | 10 | 11 | 21 | 22 | 53 | 18 |
2-Mar-2016 | 12 | 13 | 44 | 52 | 62 | 19 |
5-Mar-2016 | 3 | 27 | 34 | 59 | 69 | 19 |
9-Mar-2016 | 14 | 23 | 32 | 34 | 68 | 3 |
<tbody abp="826">
</tbody><colgroup abp="1022"><col abp="1023"><col span="5" abp="1024"><col abp="1025"></colgroup>