# Formula for larger numbered mode if 2 modes are equal

##### New Member
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?

#### shg

##### MrExcel MVP
 a​ b​ 2​ 2​ a2: =max(mode.mult(a3:a13)) 3​ 1​ 4​ 1​ 5​ 1​ 6​ 1​ 7​ 2​ 8​ 2​ 9​ 2​ 10​ 2​ 11​ 3​ 12​ 3​ 13​ 3​

#### Marcelo Branco

##### MrExcel MVP
Welcome to Mr Excel forum

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

M.

##### New Member
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!

 3 3 2 4 3 2 2 2 2 3 4 4 4 3 3 2 3 3 2 2 4 1 3 2 2 4 4 3 2 3 2 3 3 2 2

<tbody>
</tbody>

#### Marcelo Branco

##### MrExcel MVP
Maybe something like this

 A​ B​ C​ D​ E​ F​ G​ H​ I​ J​ K​ L​ M​ N​ 1​ Result1​ Result2​ Result3​ Result4​ Result5​ 2​ 3​ 2​ 3​ 4​ 3​ 2​ 2​ Student1​ 3​ 2​ 4​ 3​ 2​ 2​ 3​ 4​ 4​ 4​ 3​ Student2​ 4​ 3​ 2​ 4​ 3​ 2​ 3​ 3​ 2​ 2​ 4​ Student3​ 3​ 2​ 4​ 5​ 1​ 3​ 2​ 2​ 4​ 4​ 3​ Student4​ 4​ 3​ 2​ 1​ 6​ 2​ 3​ 2​ 3​ 3​ 2​ 2​ Student5​ 2​ 3​ 7​

<tbody>
</tbody>

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.

##### New Member
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"
 A B C D 1 3 4 3 4 2 3 2 3 3 3 4 4 3 3

<tbody>
</tbody>

In another sheet called "Modes", I have the following:
 A B C D E 1 Standard1 2 Mode 2nd Mode 3rd Mode 4th Mode 3 S1 4 S2 5 S3

<tbody>
</tbody>

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

##### MrExcel MVP
Say your data in Standard 1 is

 A​ B​ C​ D​ E​ 1​ Sudent​ Value1​ Value2​ Value3​ Value4​ 2​ S1​ 3​ 4​ 3​ 4​ 3​ S2​ 3​ 2​ 3​ 3​ 4​ S3​ 4​ 4​ 3​ 3​

Sheet Modes

 A​ B​ C​ D​ E​ 1​ Student​ Mode​ 2nd Mode​ 3rd Mode​ 4th Mode​ 2​ S1​ 4​ 3​ 3​ S2​ 3​ 2​ 4​ S3​ 4​ 3​

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.

##### New Member
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

##### MrExcel MVP
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.

Last edited:

1,081,969
Messages
5,362,484
Members
400,677
Latest member
champchamp

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...