Mode function

G

Guy Lydig

Why does the MODE function return only the largest value if an array of
numbers has more than one mode? How can I get it to return all the modes?

TIA

Guy
 
G

Guy Lydig

I'm sorry--it does not return the largest value, it returns the uppermost
value. Why?

Example
1
1
2
2
4
Mode = 1

2
2
1
1
4
Mode = 2

Really the Modes are 1 and 2.
 
T

Tyro

Read my answer again. In your case, both 1 and 2 are the most repeating
values. In your first example, MODE returns 1, which occurs as often as 2
and 1 appears first, so it is returned. In your second example 2, occurs as
often as 1 and 2 occurs first, so it is returned.

Tyro
 
G

Guy Lydig

I read and understood your answer, and as you can see from my second post, I
discovered that the top value is returned rather than the largest.

My question now is what do I have to do to get all the modes rather than
just the top one?

Thanks
 
T

Tyro

There is only one MODE. Are you asking how many times a certain value occurs
in a column? And you want a list of each, in your case, 1 occurs 2 times, 2
occurs 2 times and 4 occurs once?


Tyro
 
R

Ron Coderre

With
A1:A11 containing this list:
1
2
3
2
2
3
3
4
5
6
(blank)

This formula returns the count of modes:
C1:
=SUM(--(FREQUENCY($A$1:$A$10,$A$1:$A$10)=MAX(FREQUENCY($A$1:$A$10,$A$1:$A$10))))

This formula returns the MODES in order of occurrence:
D1: =LARGE(INDEX((FREQUENCY($A$1:$A$10,$A$1:$A$10)=
MAX(FREQUENCY($A$1:$A$10,$A$1:$A$10)))*$A$1:$A$11,0),ROWS($1:1))

Copy D1 down as far as you need.

In the above example, there are 2 modes and the formulas return....
D1: 3
D2: 2
D3: ""

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Top