Formula for larger numbered mode if 2 modes are equal

[Gladiator1018]

Hi all - new here but have been searching out answers for a grading program I'm using at school. For this program, we calculate the most common number but also the 2nd most common number. If those are equal, it typically spits out the first number in the sequence - when I'd like it to spit out the higher number of the 2. Is that possible and would anyone be able to help me with that particular code/function?

Thank you in advance!

 

[shg]

[table="width:, class:grid"][tr][td="bgcolor:#c0c0c0"][/td][td="bgcolor:#c0c0c0"]

a​

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

b​

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

2​

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

2​

[/td][td]a2: =max(mode.mult(a3:a13))[/td][/tr]
[tr][td="bgcolor:#c0c0c0"]

3​

[/td][td]

1​

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

4​

[/td][td]

1​

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

5​

[/td][td]

1​

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

6​

[/td][td]

1​

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

7​

[/td][td]

2​

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

8​

[/td][td]

2​

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

9​

[/td][td]

2​

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

10​

[/td][td]

2​

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

11​

[/td][td]

3​

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

12​

[/td][td]

3​

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

13​

[/td][td]

3​

[/td][td][/td][/tr]
[/table]

 

[Marcelo Branco]

Welcome to Mr Excel forum

Could you provide an example - a small data sample along with expected results?

M.

 

[Gladiator1018]

Welcome to Mr Excel forum

Could you provide an example - a small data sample along with expected results?

M.

You bet - thank you for looking at it! Assume the following is a set of scores from 5 students - the columns represent assignments and the rows represent different students. I would like to find the most common score (mode) and the 2nd most common score. However, if one of those values is the same, I'd like the higher number to be selected and the lower number to be used as the 2nd value. For example, in row one, there are (3) 3's and (3) 2's. I'd like 3 to go in the first box and 2 to go in the 2nd...not 2,3. Is that possible? Thanks for any help!

[TABLE="width: 500"]
<tbody>[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

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

B​

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

C​

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

D​

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

E​

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

F​

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

G​

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

H​

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

I​

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

J​

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

K​

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

L​

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

M​

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

N​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Result1​

[/TD]
[TD]

Result2​

[/TD]
[TD]

Result3​

[/TD]
[TD]

Result4​

[/TD]
[TD]

Result5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

Student1​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

Student2​

[/TD]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

Student3​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

Student4​

[/TD]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

Student5​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in J2 copied across and down
=IFERROR(MAX(MODE.MULT(IF(ISNA(MATCH($A2:$G2,$I2:I2,0)),$A2:$G2+{0;0}))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Gladiator1018]

Still not working for some reason...here is as best as description as I can get (and I don't know how to make the tables on this forum like you do yet....sorry about that):

Here is a data table from the sheet called "Standard 1"
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]








In another sheet called "Modes", I have the following:
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Standard1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Mode[/TD]
[TD]2nd Mode[/TD]
[TD]3rd Mode[/TD]
[TD]4th Mode[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]S1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]S2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]S3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

When I copy the formula and plug in the new cells, it comes up as a number of zero for some reason. And I'm not sure what the purpose/function of the "$I2:I2, 0" is in the previous formula....

Thanks for any help in advance!

MT

 

[Marcelo Branco]

Say your data in Standard 1 is

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

A​

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

B​

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

C​

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

D​

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

E​

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

1​

[/td][td]

Sudent​

[/td][td]

Value1​

[/td][td]

Value2​

[/td][td]

Value3​

[/td][td]

Value4​

[/td][/tr]

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

2​

[/td][td]

S1​

[/td][td]

3​

[/td][td]

4​

[/td][td]

3​

[/td][td]

4​

[/td][/tr]

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

3​

[/td][td]

S2​

[/td][td]

3​

[/td][td]

2​

[/td][td]

3​

[/td][td]

3​

[/td][/tr]

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

4​

[/td][td]

S3​

[/td][td]

4​

[/td][td]

4​

[/td][td]

3​

[/td][td]

3​

[/td][/tr]
[/table]

Sheet Modes

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

A​

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

B​

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

C​

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

D​

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

E​

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

1​

[/td][td]

Student​

[/td][td]

Mode​

[/td][td]

2nd Mode​

[/td][td]

3rd Mode​

[/td][td]

4th Mode​

[/td][/tr]

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

2​

[/td][td]

S1​

[/td][td]

4​

[/td][td]

3​

[/td][td][/td][td][/td][/tr]

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

3​

[/td][td]

S2​

[/td][td]

3​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]

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

4​

[/td][td]

S3​

[/td][td]

4​

[/td][td]

3​

[/td][td][/td][td][/td][/tr]
[/table]

Array formula in B2 copied across and down
=IFERROR(MAX(MODE.MULT(IF(ISNA(MATCH(INDEX('Standard 1'!$B:$E,MATCH($A2,'Standard 1'!$A:$A,0),0),$A2:A2,0)),INDEX('Standard 1'!$B:$E,MATCH($A2,'Standard 1'!$A:$A,0),0)+{0;0}))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Gladiator1018]

It's still not working....probably because you are referencing "MATCH($A2,'Standard 1'!$A:$A,0),0)" and I don't have students listed in the Standard 1 sheet....data starts in cell A1. Thanks for your time!

 

[Marcelo Branco]

It's still not working....probably because you are referencing "MATCH($A2,'Standard 1'!$A:$A,0),0)" and I don't have students listed in the Standard 1 sheet....data starts in cell A1. Thanks for your time!

There must be student names next to their notes. Otherwise, how to identify which student belongs the grades in rows 1, 2, 3 ...???

M.