Using the mode function

[Detra E.]

I have a group of numbers that I am trying to find the the mode of using the
mode function. When I have multiple mode i.e 200 shows twice and 201 shows
twice the standard function returns the lower of the two modes and I want it
to return the higher of the two modes. Is there anyone who can help me figure
this out.

 

[Leo Rod]

Try
=Mode(max(values))
let me know.

 

[T. Valko]

MODE returns the *first* mode of the range. For example:

200
200
201
201

MODE = 200

201
201
200
200

MODE = 201

Try this array formula**. I'm assuming there is at least one mode:

=MAX(IF(COUNTIF(A1:A10,A1:A10)=MAX(COUNTIF(A1:A10,A1:A10)),A1:A10))

If there is no mode the above formula will still return a value. You can add
a test that returns a blank (or whatever you want) if there is no mode:

=IF(ISNA(MODE(A1:A10)),"",MAX(IF(COUNTIF(A1:A10,A1:A10)=MAX(COUNTIF(A1:A10,A1:A10)),A1:A10)))

 

[Harlan Grove]

Try
=Mode(max(values))
let me know.

....

Learn to test before responding. MAX(anything) returns only one value,
and MODE(just_one_value) *ALWAYS* returns #N/A. MODE will only return
a value when passed values, arrays or ranges that contain at least two
equal values. So MODE(1,2) returns #N/A, while MODE(3,3) returns 3.

 

[T. Valko]

I forgot to include something in my reply:

Try this array formula**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Another possible option is to sort your range of numbers in descending order
then the normal formula would work:

=MODE(range)

 

[Harlan Grove]

MODE returns the *first* mode of the range. For example: ....
. . . You can add a test that returns a blank (or whatever you
want) if there is no mode:

=IF(ISNA(MODE(A1:A10)),"",
MAX(IF(COUNTIF(A1:A10,A1:A10)=MAX(COUNTIF(A1:A10,A1:A10)),A1:A10)))

Do less work, get the same result.

=IF(ISNA(MODE(x)),"",MAX(IF(COUNTIF(x,x)=COUNTIF(x,MODE(x)),x)))

 

[Harlan Grove]

Another possible option is to sort your range of numbers in
descending order then the normal formula would work:

=MODE(range)

In that vein, define a name like seq referring to

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$65536,1024,1))

(change the 1024 to whatever is the best trade-off between performance
and inclusiveness), and use array formulas like

=MODE(IF(seq<=COUNT(Data),LARGE(Data,seq)))

This does more work than

=MAX(IF(COUNTIF(Data,Data)=COUNTIF(Data,MODE(Data)),Data))

 

[T. Valko]

Do less work, get the same result.

=IF(ISNA(MODE(x)),"",MAX(IF(COUNTIF(x,x)=COUNTIF(x,MODE(x)),x)))

Yep, that "works" for me!