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.
--
Detra (Thanks)

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

"Detra E." wrote in message
...
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.
--
Detra (Thanks)

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)))

--
Biff
Microsoft Excel MVP


"Detra E." wrote in message
...
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.
--
Detra (Thanks)

"Leo Rod" <leo@rod_com wrote...
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.

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)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)))

--
Biff
Microsoft Excel MVP


"Detra E." wrote in message
...
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.
--
Detra (Thanks)

"T. Valko" wrote...
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)))

"T. Valko" wrote...
....
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))

"Harlan Grove" wrote in message
ups.com...
"T. Valko" wrote...
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)))


Yep, that "works" for me!

--
Biff
Microsoft Excel MVP