Mode excluding previous numbers

[Pestomania]

Hello!

In J2 I have Mode of Column B.
In J3 I want to have mode of column B excluding numbers equal to J2.
In J4 I want to have mode of column B excluding J2 and J3 and so forth.

I was able to get J3 to work with =Mode(if(B$2:B$107<>J2, B$2,B$107))

That works perfect. I tried adding and and function to keep it going but can't get it to work.

=Mode(if(and(B$2:B$107<>J2, B$2:B$107<>J3 , B$2,B$107))

I am using [Ctrl]+[Shift]+[Enter]

 

[Peter_SSs]

Can you clarify exactly what you would want (& why) in J2, J3, J4, J5 & J6 for this sample data?

Excel 2016
	B
1
2	2
3	3
4	2
5	3
6	2
7	3
8	5
9	6
10	6
11	6
12	4
13	4
14	5
15
Mode

 

[Pestomania]

So J2 would get the "first mode" which in this case is 2. Then J3 would look for the "second mode" which would be 3. J4 would look at the "third mode" which is 6.

Each time it calculates the mode, I want it to exclude the previous mode.

 

[Peter_SSs]

So J2 would get the "first mode" which in this case is 2. Then J3 would look for the "second mode" which would be 3. J4 would look at the "third mode" which is 6.

Each time it calculates the mode, I want it to exclude the previous mode.

OK, try this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Note that this will not list single values. Is that an issue for you?

Excel Workbook
	B	J
1
2	2	2
3	3	3
4	2	6
5	3	5
6	2	4
7	3
8	5
9	6
10	6
11	6
12	4
13	4
14	5
15
Mode

 

[Peter_SSs]

Note that this will not list single values.

To allow for that, and possible blank cells in the range, try this instead. Still confirmed with Ctrl+Shift+Enter.

Excel Workbook
	B	J
1
2	2	2
3	3	3
4	2	6
5	3	5
6	2	17
7	3	4
8	5
9	6
10	6
11	6
12	17
13	4
14	5
15
Mode

 

[Pestomania]

To allow for that, and possible blank cells in the range, try this instead. Still confirmed with Ctrl+Shift+Enter.

Excel Workbook
	B	J
1
2	2	2
3	3	3
4	2	6
5	3	5
6	2	17
7	3	4
8	5
9	6
10	6
11	6
12	17
13	4
14	5
15
Mode

I tried this program. Unfortunately it didn't include all options. For whatever it stopped calculating after a while. I cannot find the reason why it's not pulling all of them.

In the column there are 50 unique values (multiple occurrences), but it's only pulling 30.

 

[Marcelo Branco]

Another option to list single values

	B​	J​
1​	Values​	Result​
2​	2​	2​
3​	3​	3​
4​	2​	6​
5​	3​	5​
6​	2​	17​
7​	3​	4​
8​	5​	8​
9​	6​	11​
10​	6​
11​	6​
12​	17​
13​	4​
14​	5​
15​
16​	8​
17​
18​	11​
19​

Array formula in J2 copied down
=IFERROR(MODE(IF(B$2:B$107<>"",IF(ISNA(MATCH(B$2:B$107,J$1:J1,0)),B$2:B$107+{0,0}))),"")
confirmed with Ctrl+Shift+Enter

M.

 

[Peter_SSs]

In the column there are 50 unique values

Are the values still only in the range B2:B107? If a larger range, did you adjust the formula for that?

 

[Pestomania]

Are the values still only in the range B2:B107? If a larger range, did you adjust the formula for that?

Hello,

I did adjust the range and it is working!!! Thank you!!

 

[Peter_SSs]

Hello,

I did adjust the range and it is working!!! Thank you!!

Great. Glad you have it working now.