CODEWITHSUNDEEP
excelvbaexcel-formula
PAS
PAS

Reputation: 89

How to select highest among most frequent number in excel?

In this below example how do we set mode to 2 instead of 1. Basically if there are two instances of most frequent occurring number, how do we select the highest among them?

enter image description here

Upvotes: 3

Views: 103

Answers (2)

Terry W
Terry W

Reputation: 3257

If you are using Excel 2010 or later versions, presume your data is stored in A2:E2, try one of the following:

=AGGREGATE(14,4,MODE.MULT($A$2:$E$2),1)

or

=MAX(MODE.MULT($A$2:$E$2))

or

=LARGE(MODE.MULT($A$2:$E$2),1)

Upvotes: 2

JvdV
JvdV

Reputation: 75840

This formula would do the job:

=MODE(LARGE(A2:E2,ROW(1:5)))

enter image description here

It would work in a few stept; first ROW(1:5) returns an array of numbers to evaluate with LARGE so it would look like > =MODE(LARGE(A2:E2,{1,2,3,4,5})). Next, LARGE will put the range in order of 1st largest to 5th largest so it would look like > =MODE({3,2,2,1,1}). Now MODE can pick up the largest most frequent value.

Upvotes: 5

Related Questions