Using the mode function
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)
|