Mode excluding previous numbers

Pestomania · Oct 20, 2018

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 · Oct 20, 2018

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

Excel 2016

B

1

2

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 · Oct 20, 2018

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 · Oct 20, 2018

Pestomania said:
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

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 · Oct 20, 2018

Peter_SSs said:
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

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 · Oct 24, 2018

Peter_SSs said:
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 · Oct 24, 2018

Another option to list single values

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

B

[/td][td="bgcolor: #DCE6F1"]

J

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1

[/td][td]

Values

[/td][td]

Result