Formula for larger numbered mode if 2 modes are equal

Gladiator1018

New Member
Joined
Oct 24, 2018
Messages
5
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
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
Joined
Aug 23, 2010
Messages
16,317
Welcome to Mr Excel forum

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

M.
 

Gladiator1018

New Member
Joined
Oct 24, 2018
Messages
5
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!

3324322
2234443
3233224
1322443
2323322

<tbody>
</tbody>
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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.
 

Gladiator1018

New Member
Joined
Oct 24, 2018
Messages
5
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"
ABCD
1
3434
23233
34433

<tbody>
</tbody>








In another sheet called "Modes", I have the following:
ABCDE
1Standard1
2Mode2nd Mode3rd Mode4th Mode
3S1
4S2
5S3

<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
Joined
Aug 23, 2010
Messages
16,317
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.
 

Gladiator1018

New Member
Joined
Oct 24, 2018
Messages
5
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
Joined
Aug 23, 2010
Messages
16,317
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:

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top