Mode function

[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

 

[Tyro]

MODE does not return the largest value. It returns the most frequently
occurring, or repetitive, value in an array or range of data. If more than
one value meets the criterion, MODE returns the first value that does.

Tyro

 

[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.

 

[Guy Lydig]

Thanks for your response. How can I get it to return all modes?

 

[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

 

[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

 

[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

 

[Dave Peterson]

Take a look at these posts:

UDF:
http://snipurl.com/1zlva
Bernie Detrick

Formulas:
http://snipurl.com/1zlv4
(Harlan Grove)

 

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