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

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

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

"Tyro" wrote:

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

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" wrote:

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

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" wrote in message
...
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" wrote:

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

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" wrote:

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" wrote in message
...
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" wrote:

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

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

"Guy Lydig" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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

Take a look at these posts:

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

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




Guy Lydig wrote:

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

--

Dave Peterson

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)

"Guy Lydig" wrote in message
...
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