MODE vs INDEX vs LOOKUP

[William Groff]

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.

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>

 

[Canapone]

Hi,

if J2 houses

=MODE(B$4:B$108)

in J3 (array entered) to be copied across:

=MODE(IF(COUNTIF(J$2:J2,B$4:B$108)=0,B$4:B$108))

Hope it helps

 

[Aladin Akyurek]

@William Groff

Does the upper exhibit showing your formula results or the desired results i.e. the results which must obtain?

 

[Joe4]

Cross posted here: MODE vs INDEX

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.