MODE after expecific number

[fjgg4se]

hi

i have a list of ramdom numbers between 0 and 9, and i got a table where i want to find the mode of 1st, 2nd and 3th most common number after a expecfic number.

https://ibb.co/k5z6rG

 

[Andrew Fergus]

Hello

I'm not understanding your question 100% and would prefer if you posted the full question here - in years to come that link may not work and people looking at this thread won't have the full context.

There is an in-built function in Excel to get the mode value.

Assuming the data is in cells B2:B16 then the mode is calculated like this:
=MODE(B2:B16)

To get the 2nd mode (in other words the second most frequent number), you can use an array formula like this where the value from the first formula is in cell F2:
{=MODE(IF(B2:B16<>F2,B2:B16))}
This is an array formula - do NOT enter the curly brackets{}, instead enter the formula without the curly brackets and before you press the Enter key, instead press Ctrl & Shift and while holding these 2 keys down then press the Enter key. The curly brackets will appear automatically in the formula bar.

To get the 3rd mode, the array formula looks like this:
{=MODE(IF(B2:B16<>F2,IF(B2:B16<>F3,B2:B16)))}
same deal with the Ctrl + Shift + Enter.
Again, this assumes the first mode has already been worked out in cell F2 and the second mode value has been worked out in cell F3.

I trust this helps.
Andrew

 

[Joe4]

I have noticed that all your previous questions have been about "Google Sheets" and not "Excel".
Is this an Excel or Google Sheets question?

Note, for all your Google Sheets questions, you should do the following:
- Post them to the "General Excel Discussion & Other Questions" forum
- Include "GOOGLE SHEETS" in the title of your post, to alert people that this is a question about Google Sheets

Thank you

 

[wideboydixon]

Solution for next number using only formulas:

Book1
	A	B	C	D	E	F
1	LIST		MODE exactly after	MODE in the next 9 numbers	Second most common in the next 9	Third most common number in the next 9 numbers
2	5	0	2
3	4	1	5
4	7	2	3
5	8	3	2
6	9	4	1
7	6	5	7
8	3	6	3
9	2	7	8
10	1	8	9
11	5	9	6
12	7
13	0
14	9
15	6
16	5
17	4
18	1
19	2
20	3
21	3
22	5
23	7
24	7
25	8
26	9
27	2
28	0
29	2
30	5
31	7
32	8
33	5
34	6
35	6
36	8
37	4
38	9
39	5
40	7
41	5
42	0
43	2
44	3
45	6
46	9
47	8
48	7
49	4
50	1
51	5
52	9
53	8
54	7
55	6
56	3
57	2
58	5
59	4
Sheet1

Cell Formulas
Range	Formula
C2	{=MODE(IF($A$1:$A$58=$B2,$A$2:$A$59))}
Press CTRL+SHIFT+ENTER to enter array formulas.

I suspect the other three columns require VBA but there's no point suggesting this if you're using Google Sheets.

WBD