I'm working with lists of numbers that should return bi and multi-modal
results when analyzed.

Is there a way to get MODE() to return these results? Or is there another
method to get correct results for the modes of bi- and multi-modal data?

Thanks,
Joe Dolsak

"Jdolsak" wrote...
I'm working with lists of numbers that should return bi and multi-modal
results when analyzed.

Is there a way to get MODE() to return these results? Or is there another
method to get correct results for the modes of bi- and multi-modal data?

MODE returns the first mode it finds. If you had (grossly oversimplified)
data like the following in A1:J10

7 1 9 3 0 7 0 5 1 9
4 7 5 5 3 2 5 4 3 7
2 5 9 9 7 6 8 2 1 5
1 0 7 6 6 2 3 3 0 9
7 8 6 1 0 5 1 9 7 3
3 5 8 7 2 0 5 8 3 0
8 1 8 5 1 3 9 1 7 4
5 9 0 3 1 6 6 8 5 7
7 2 3 3 1 1 2 3 5 5
6 7 6 7 1 1 8 0 3 4

MODE(A1:J10) would be 7 because 7 is one of the 4 numbers with 14 instances
and it appears before any of the other 3. If you want the next mode, use the
array formula

=MODE(IF(A1:J10<7,A1:J10))

or

=MODE(IF(A1:J10<MODE(A1:J10),A1:J10))

You could continue with the brute force approach for subsequent modes, but
there's a more elegant way to do this. If the modes would be recorded in
column L beginning in cell L1, use the following formulas.

L1:
=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),"")

L2 [array formula]:
=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
MODE($A$1:$J$10))))=ROWS(L$1:L2),MODE(IF(COUNTIF( L$1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

Fill L2 down until it returns "".

"Harlan Grove" wrote:

"Jdolsak" wrote...
I'm working with lists of numbers that should return bi and multi-modal
results when analyzed.

Is there a way to get MODE() to return these results? Or is there another
method to get correct results for the modes of bi- and multi-modal data?

MODE returns the first mode it finds. If you had (grossly oversimplified)
data like the following in A1:J10

7 1 9 3 0 7 0 5 1 9
4 7 5 5 3 2 5 4 3 7
2 5 9 9 7 6 8 2 1 5
1 0 7 6 6 2 3 3 0 9
7 8 6 1 0 5 1 9 7 3
3 5 8 7 2 0 5 8 3 0
8 1 8 5 1 3 9 1 7 4
5 9 0 3 1 6 6 8 5 7
7 2 3 3 1 1 2 3 5 5
6 7 6 7 1 1 8 0 3 4

MODE(A1:J10) would be 7 because 7 is one of the 4 numbers with 14 instances
and it appears before any of the other 3. If you want the next mode, use the
array formula

=MODE(IF(A1:J10<7,A1:J10))

or

=MODE(IF(A1:J10<MODE(A1:J10),A1:J10))

You could continue with the brute force approach for subsequent modes, but
there's a more elegant way to do this. If the modes would be recorded in
column L beginning in cell L1, use the following formulas.

L1:
=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),"")

L2 [array formula]:
=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
MODE($A$1:$J$10))))=ROWS(L$1:L2),MODE(IF(COUNTIF( L$1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

Fill L2 down until it returns "".


That formula doesn't like zeroes. If I have meaningful zeroes in my data,
the formula throws them into the ranked list in the wrong place (i.e. the
zeroes are less frequent than those further down the list).

Rothman wrote...
"Harlan Grove" wrote:
....
You could continue with the brute force approach for subsequent modes, but
there's a more elegant way to do this. If the modes would be recorded in
column L beginning in cell L1, use the following formulas.

L1:
=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),"")

L2 [array formula]:
=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
MODE($A$1:$J$10))))=ROWS(L$1:L2),MODE(IF(COUNTI F(L$1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

Fill L2 down until it returns "".

That formula doesn't like zeroes. If I have meaningful zeroes in my data,
the formula throws them into the ranked list in the wrong place (i.e. the
zeroes are less frequent than those further down the list).

I don't know what you did wrong, but with the following revised sample
data in A1:J10,

7 1 9 0 0 7 0 5 1 9
4 7 5 5 3 2 5 4 3 7
2 5 9 9 7 6 8 2 1 5
1 0 7 6 6 2 3 3 0 9
7 8 6 1 0 5 1 9 7 3
3 5 8 7 2 0 5 8 0 0
8 1 8 5 1 3 9 1 7 4
5 9 0 0 1 6 6 8 5 7
7 2 3 0 1 1 2 0 5 5
6 7 6 7 1 1 8 0 3 4

and the following formulas

L1:
=IF(COUNT(MODE($A$1:$J$10)),MODE($A$1:$J$10),"")

L2 [array formula]:
=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
MODE($A$1:$J$10))))ROWS(L$1:L1),MODE(IF(COUNTIF(L $1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

L2 filled down into L3:L5, then L1:L5 evaluates to {7;1;0;5;""}. The
formula returns zero when zero is a mode. So what did you do wrong? Are
your values unrounded formula results that just appear to be zero but
are actually very small nonzero (and unequal) values?

I had blank cells included in my range, that's what I did wrong.

Excuse me while I remove egg from my face.

Thanks so much, though, for verifying that I was being an idiot.

All in all, finding out additional modes should be a heck of a lot easier
than it is. I'm not holding my breath for Excel 2007, though.

Thanks again!

"Harlan Grove" wrote:

Rothman wrote...
"Harlan Grove" wrote:
....
You could continue with the brute force approach for subsequent modes, but
there's a more elegant way to do this. If the modes would be recorded in
column L beginning in cell L1, use the following formulas.

L1:
=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),"")

L2 [array formula]:
=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
MODE($A$1:$J$10))))=ROWS(L$1:L2),MODE(IF(COUNTI F(L$1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

Fill L2 down until it returns "".

That formula doesn't like zeroes. If I have meaningful zeroes in my data,
the formula throws them into the ranked list in the wrong place (i.e. the
zeroes are less frequent than those further down the list).

I don't know what you did wrong, but with the following revised sample
data in A1:J10,

7 1 9 0 0 7 0 5 1 9
4 7 5 5 3 2 5 4 3 7
2 5 9 9 7 6 8 2 1 5
1 0 7 6 6 2 3 3 0 9
7 8 6 1 0 5 1 9 7 3
3 5 8 7 2 0 5 8 0 0
8 1 8 5 1 3 9 1 7 4
5 9 0 0 1 6 6 8 5 7
7 2 3 0 1 1 2 0 5 5
6 7 6 7 1 1 8 0 3 4

and the following formulas

L1:
=IF(COUNT(MODE($A$1:$J$10)),MODE($A$1:$J$10),"")

L2 [array formula]:
=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
MODE($A$1:$J$10))))ROWS(L$1:L1),MODE(IF(COUNTIF(L $1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

L2 filled down into L3:L5, then L1:L5 evaluates to {7;1;0;5;""}. The
formula returns zero when zero is a mode. So what did you do wrong? Are
your values unrounded formula results that just appear to be zero but
are actually very small nonzero (and unequal) values?

Ah, but try this one on for size. Below are 75 numbers in a single column.
Excel tells me the number 1 is the mode. Using countif, I get a frequency of
4.

However, your formula reveals the true mode, albeit in a strange place.
-1.66666667 shows up 7 times in this set of numbers -- it should be the mode!
And yet Excel insists on the number 1 when I use the simple Mode function
(=MODE()).

And here's something else that is goofy: =Mode() gives me the correct
number when the numbers are organized lowest to highest, but when they're in
the order I need them to be in, it resorts back to the wrong mode (#1).

There's something amiss here.

0.833333333
0.333333333
-1.833333333
-0.833333333
3
-0.333333333
-2
1
-1.666666667
2
-1.166666667
1.5
0
-1
-1.333333333
4.166666667
-3
-0.166666667
-2.5
2.166666667
2
-2.5
0.5
1.333333333
0.166666667
-0.833333333
0.5
-1.666666667
2.333333333
1
-1.5
-1
-1.166666667
2.166666667
-0.833333333
0.833333333
1.666666667
-1.666666667
0.666666667
-1.666666667
2
-1.833333333
-0.166666667
0.333333333
-0.333333333
-0.666666667
-1.666666667
2.833333333
-1
-0.166666667
1.5
-1.666666667
-0.5
3.166666667
-2.833333333
1.5
1.166666667
1.333333333
-2.666666667
0.5
0.166666667
0
-1.666666667
0.666666667
0.166666667
0.333333333
0.833333333
0
2
-2.666666667
1
-0.666666667
1.333333333
-2
1


"Harlan Grove" wrote:

Rothman wrote...
"Harlan Grove" wrote:
....
You could continue with the brute force approach for subsequent modes, but
there's a more elegant way to do this. If the modes would be recorded in
column L beginning in cell L1, use the following formulas.

L1:
=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),"")

L2 [array formula]:
=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
MODE($A$1:$J$10))))=ROWS(L$1:L2),MODE(IF(COUNTI F(L$1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

Fill L2 down until it returns "".

That formula doesn't like zeroes. If I have meaningful zeroes in my data,
the formula throws them into the ranked list in the wrong place (i.e. the
zeroes are less frequent than those further down the list).

I don't know what you did wrong, but with the following revised sample
data in A1:J10,

7 1 9 0 0 7 0 5 1 9
4 7 5 5 3 2 5 4 3 7
2 5 9 9 7 6 8 2 1 5
1 0 7 6 6 2 3 3 0 9
7 8 6 1 0 5 1 9 7 3
3 5 8 7 2 0 5 8 0 0
8 1 8 5 1 3 9 1 7 4
5 9 0 0 1 6 6 8 5 7
7 2 3 0 1 1 2 0 5 5
6 7 6 7 1 1 8 0 3 4

and the following formulas

L1:
=IF(COUNT(MODE($A$1:$J$10)),MODE($A$1:$J$10),"")

L2 [array formula]:
=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
MODE($A$1:$J$10))))ROWS(L$1:L1),MODE(IF(COUNTIF(L $1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

L2 filled down into L3:L5, then L1:L5 evaluates to {7;1;0;5;""}. The
formula returns zero when zero is a mode. So what did you do wrong? Are
your values unrounded formula results that just appear to be zero but
are actually very small nonzero (and unequal) values?

Rothman -

I pasted the 75 numbers into Excel, and the MODE function
returns -1.666666667.

I may have missed parts of this thread, but I suggest you answer an
extension of one of the original questions: "Are your values unrounded
formula results that just appear to be zero but are actually very small
nonzero (and unequal) values?"

That is, are some of your 75 numbers the result of formulas?

If so, that could explain the differences between the 75
ten-significant-digit numbers in your message and the 75
fifteen-significant-digit results of formulas on your worksheet.

- Mike
www.mikemiddleton.com

"Rothman" wrote in message
...
Ah, but try this one on for size. Below are 75 numbers in a single
column.
Excel tells me the number 1 is the mode. Using countif, I get a frequency
of
4.

However, your formula reveals the true mode, albeit in a strange place.
-1.66666667 shows up 7 times in this set of numbers -- it should be the
mode!
And yet Excel insists on the number 1 when I use the simple Mode function
(=MODE()).

And here's something else that is goofy: =Mode() gives me the correct
number when the numbers are organized lowest to highest, but when they're
in
the order I need them to be in, it resorts back to the wrong mode (#1).

There's something amiss here.

0.833333333
0.333333333
-1.833333333
-0.833333333
3
-0.333333333
-2
1
-1.666666667
2
-1.166666667
1.5
0
-1
-1.333333333
4.166666667
-3
-0.166666667
-2.5
2.166666667
2
-2.5
0.5
1.333333333
0.166666667
-0.833333333
0.5
-1.666666667
2.333333333
1
-1.5
-1
-1.166666667
2.166666667
-0.833333333
0.833333333
1.666666667
-1.666666667
0.666666667
-1.666666667
2
-1.833333333
-0.166666667
0.333333333
-0.333333333
-0.666666667
-1.666666667
2.833333333
-1
-0.166666667
1.5
-1.666666667
-0.5
3.166666667
-2.833333333
1.5
1.166666667
1.333333333
-2.666666667
0.5
0.166666667
0
-1.666666667
0.666666667
0.166666667
0.333333333
0.833333333
0
2
-2.666666667
1
-0.666666667
1.333333333
-2
1


"Harlan Grove" wrote:

Rothman wrote...
"Harlan Grove" wrote:
....
You could continue with the brute force approach for subsequent modes,
but
there's a more elegant way to do this. If the modes would be recorded
in
column L beginning in cell L1, use the following formulas.

L1:
=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),"")

L2 [array formula]:
=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
MODE($A$1:$J$10))))=ROWS(L$1:L2),MODE(IF(COUNTI F(L$1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

Fill L2 down until it returns "".

That formula doesn't like zeroes. If I have meaningful zeroes in my
data,
the formula throws them into the ranked list in the wrong place (i.e.
the
zeroes are less frequent than those further down the list).

I don't know what you did wrong, but with the following revised sample
data in A1:J10,

7 1 9 0 0 7 0 5 1 9
4 7 5 5 3 2 5 4 3 7
2 5 9 9 7 6 8 2 1 5
1 0 7 6 6 2 3 3 0 9
7 8 6 1 0 5 1 9 7 3
3 5 8 7 2 0 5 8 0 0
8 1 8 5 1 3 9 1 7 4
5 9 0 0 1 6 6 8 5 7
7 2 3 0 1 1 2 0 5 5
6 7 6 7 1 1 8 0 3 4

and the following formulas

L1:
=IF(COUNT(MODE($A$1:$J$10)),MODE($A$1:$J$10),"")

L2 [array formula]:
=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
MODE($A$1:$J$10))))ROWS(L$1:L1),MODE(IF(COUNTIF(L $1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

L2 filled down into L3:L5, then L1:L5 evaluates to {7;1;0;5;""}. The
formula returns zero when zero is a mode. So what did you do wrong? Are
your values unrounded formula results that just appear to be zero but
are actually very small nonzero (and unequal) values?