software

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
quot;Jdolsakquot; gt; wrote...
gt;I'm working with lists of numbers that should return bi and multi-modal
gt;results when analyzed.
gt;
gt;Is there a way to get MODE() to return these results? Or is there another
gt;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:J10lt;gt;7,A1:J10))

or

=MODE(IF(A1:J10lt;gt;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),quot;quot;)

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

Fill L2 down until it returns quot;quot;.
quot;Harlan Grovequot; wrote:

gt; quot;Jdolsakquot; gt; wrote...
gt; gt;I'm working with lists of numbers that should return bi and multi-modal
gt; gt;results when analyzed.
gt; gt;
gt; gt;Is there a way to get MODE() to return these results? Or is there another
gt; gt;method to get correct results for the modes of bi- and multi-modal data?
gt;
gt; MODE returns the first mode it finds. If you had (grossly oversimplified)
gt; data like the following in A1:J10
gt;
gt; 7 1 9 3 0 7 0 5 1 9
gt; 4 7 5 5 3 2 5 4 3 7
gt; 2 5 9 9 7 6 8 2 1 5
gt; 1 0 7 6 6 2 3 3 0 9
gt; 7 8 6 1 0 5 1 9 7 3
gt; 3 5 8 7 2 0 5 8 3 0
gt; 8 1 8 5 1 3 9 1 7 4
gt; 5 9 0 3 1 6 6 8 5 7
gt; 7 2 3 3 1 1 2 3 5 5
gt; 6 7 6 7 1 1 8 0 3 4
gt;
gt; MODE(A1:J10) would be 7 because 7 is one of the 4 numbers with 14 instances
gt; and it appears before any of the other 3. If you want the next mode, use the
gt; array formula
gt;
gt; =MODE(IF(A1:J10lt;gt;7,A1:J10))
gt;
gt; or
gt;
gt; =MODE(IF(A1:J10lt;gt;MODE(A1:J10),A1:J10))
gt;
gt; You could continue with the brute force approach for subsequent modes, but
gt; there's a more elegant way to do this. If the modes would be recorded in
gt; column L beginning in cell L1, use the following formulas.
gt;
gt; L1:
gt; =IF(COUNT(MODE(A1:J10)),MODE(A1:J10),quot;quot;)
gt;
gt; L2 [array formula]:
gt; =IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt; MODE($A$1:$J$10))))gt;=ROWS(L$1:L2),MODE(IF(COUNTIF( L$1:L1,$A$1:$J$10)=0,
gt; $A$1:$J$10)),quot;quot;)
gt;
gt; Fill L2 down until it returns quot;quot;.
gt;

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...
gt;quot;Harlan Grovequot; wrote:
....
gt;gt;You could continue with the brute force approach for subsequent modes, but
gt;gt;there's a more elegant way to do this. If the modes would be recorded in
gt;gt;column L beginning in cell L1, use the following formulas.
gt;gt;
gt;gt;L1:
gt;gt;=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),quot;quot;)
gt;gt;
gt;gt;L2 [array formula]:
gt;gt;=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt;gt;MODE($A$1:$J$10))))gt;=ROWS(L$1:L2),MODE(IF(COUNTI F(L$1:L1,$A$1:$J$10)=0,
gt;gt;$A$1:$J$10)),quot;quot;)
gt;gt;
gt;gt;Fill L2 down until it returns quot;quot;.
gt;
gt;That formula doesn't like zeroes. If I have meaningful zeroes in my data,
gt;the formula throws them into the ranked list in the wrong place (i.e. the
gt;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),quot;quot;)

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

L2 filled down into L3:L5, then L1:L5 evaluates to {7;1;0;5;quot;quot;}. 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!

quot;Harlan Grovequot; wrote:

gt; Rothman wrote...
gt; gt;quot;Harlan Grovequot; wrote:
gt; ....
gt; gt;gt;You could continue with the brute force approach for subsequent modes, but
gt; gt;gt;there's a more elegant way to do this. If the modes would be recorded in
gt; gt;gt;column L beginning in cell L1, use the following formulas.
gt; gt;gt;
gt; gt;gt;L1:
gt; gt;gt;=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),quot;quot;)
gt; gt;gt;
gt; gt;gt;L2 [array formula]:
gt; gt;gt;=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt; gt;gt;MODE($A$1:$J$10))))gt;=ROWS(L$1:L2),MODE(IF(COUNTI F(L$1:L1,$A$1:$J$10)=0,
gt; gt;gt;$A$1:$J$10)),quot;quot;)
gt; gt;gt;
gt; gt;gt;Fill L2 down until it returns quot;quot;.
gt; gt;
gt; gt;That formula doesn't like zeroes. If I have meaningful zeroes in my data,
gt; gt;the formula throws them into the ranked list in the wrong place (i.e. the
gt; gt;zeroes are less frequent than those further down the list).
gt;
gt; I don't know what you did wrong, but with the following revised sample
gt; data in A1:J10,
gt;
gt; 7 1 9 0 0 7 0 5 1 9
gt; 4 7 5 5 3 2 5 4 3 7
gt; 2 5 9 9 7 6 8 2 1 5
gt; 1 0 7 6 6 2 3 3 0 9
gt; 7 8 6 1 0 5 1 9 7 3
gt; 3 5 8 7 2 0 5 8 0 0
gt; 8 1 8 5 1 3 9 1 7 4
gt; 5 9 0 0 1 6 6 8 5 7
gt; 7 2 3 0 1 1 2 0 5 5
gt; 6 7 6 7 1 1 8 0 3 4
gt;
gt; and the following formulas
gt;
gt; L1:
gt; =IF(COUNT(MODE($A$1:$J$10)),MODE($A$1:$J$10),quot;quot;)
gt;
gt; L2 [array formula]:
gt; =IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt; MODE($A$1:$J$10))))gt;ROWS(L$1:L1),MODE(IF(COUNTIF(L $1:L1,$A$1:$J$10)=0,
gt; $A$1:$J$10)),quot;quot;)
gt;
gt; L2 filled down into L3:L5, then L1:L5 evaluates to {7;1;0;5;quot;quot;}. The
gt; formula returns zero when zero is a mode. So what did you do wrong? Are
gt; your values unrounded formula results that just appear to be zero but
gt; are actually very small nonzero (and unequal) values?
gt;
gt;

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
1quot;Harlan Grovequot; wrote:

gt; Rothman wrote...
gt; gt;quot;Harlan Grovequot; wrote:
gt; ....
gt; gt;gt;You could continue with the brute force approach for subsequent modes, but
gt; gt;gt;there's a more elegant way to do this. If the modes would be recorded in
gt; gt;gt;column L beginning in cell L1, use the following formulas.
gt; gt;gt;
gt; gt;gt;L1:
gt; gt;gt;=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),quot;quot;)
gt; gt;gt;
gt; gt;gt;L2 [array formula]:
gt; gt;gt;=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt; gt;gt;MODE($A$1:$J$10))))gt;=ROWS(L$1:L2),MODE(IF(COUNTI F(L$1:L1,$A$1:$J$10)=0,
gt; gt;gt;$A$1:$J$10)),quot;quot;)
gt; gt;gt;
gt; gt;gt;Fill L2 down until it returns quot;quot;.
gt; gt;
gt; gt;That formula doesn't like zeroes. If I have meaningful zeroes in my data,
gt; gt;the formula throws them into the ranked list in the wrong place (i.e. the
gt; gt;zeroes are less frequent than those further down the list).
gt;
gt; I don't know what you did wrong, but with the following revised sample
gt; data in A1:J10,
gt;
gt; 7 1 9 0 0 7 0 5 1 9
gt; 4 7 5 5 3 2 5 4 3 7
gt; 2 5 9 9 7 6 8 2 1 5
gt; 1 0 7 6 6 2 3 3 0 9
gt; 7 8 6 1 0 5 1 9 7 3
gt; 3 5 8 7 2 0 5 8 0 0
gt; 8 1 8 5 1 3 9 1 7 4
gt; 5 9 0 0 1 6 6 8 5 7
gt; 7 2 3 0 1 1 2 0 5 5
gt; 6 7 6 7 1 1 8 0 3 4
gt;
gt; and the following formulas
gt;
gt; L1:
gt; =IF(COUNT(MODE($A$1:$J$10)),MODE($A$1:$J$10),quot;quot;)
gt;
gt; L2 [array formula]:
gt; =IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt; MODE($A$1:$J$10))))gt;ROWS(L$1:L1),MODE(IF(COUNTIF(L $1:L1,$A$1:$J$10)=0,
gt; $A$1:$J$10)),quot;quot;)
gt;
gt; L2 filled down into L3:L5, then L1:L5 evaluates to {7;1;0;5;quot;quot;}. The
gt; formula returns zero when zero is a mode. So what did you do wrong? Are
gt; your values unrounded formula results that just appear to be zero but
gt; are actually very small nonzero (and unequal) values?
gt;
gt;

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: quot;Are your values unrounded
formula results that just appear to be zero but are actually very small
nonzero (and unequal) values?quot;

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

quot;Rothmanquot; gt; wrote in message
...
gt; Ah, but try this one on for size. Below are 75 numbers in a single
gt; column.
gt; Excel tells me the number 1 is the mode. Using countif, I get a frequency
gt; of
gt; 4.
gt;
gt; However, your formula reveals the true mode, albeit in a strange place.
gt; -1.66666667 shows up 7 times in this set of numbers -- it should be the
gt; mode!
gt; And yet Excel insists on the number 1 when I use the simple Mode function
gt; (=MODE()).
gt;
gt; And here's something else that is goofy: =Mode() gives me the correct
gt; number when the numbers are organized lowest to highest, but when they're
gt; in
gt; the order I need them to be in, it resorts back to the wrong mode (#1).
gt;
gt; There's something amiss here.
gt;
gt; 0.833333333
gt; 0.333333333
gt; -1.833333333
gt; -0.833333333
gt; 3
gt; -0.333333333
gt; -2
gt; 1
gt; -1.666666667
gt; 2
gt; -1.166666667
gt; 1.5
gt; 0
gt; -1
gt; -1.333333333
gt; 4.166666667
gt; -3
gt; -0.166666667
gt; -2.5
gt; 2.166666667
gt; 2
gt; -2.5
gt; 0.5
gt; 1.333333333
gt; 0.166666667
gt; -0.833333333
gt; 0.5
gt; -1.666666667
gt; 2.333333333
gt; 1
gt; -1.5
gt; -1
gt; -1.166666667
gt; 2.166666667
gt; -0.833333333
gt; 0.833333333
gt; 1.666666667
gt; -1.666666667
gt; 0.666666667
gt; -1.666666667
gt; 2
gt; -1.833333333
gt; -0.166666667
gt; 0.333333333
gt; -0.333333333
gt; -0.666666667
gt; -1.666666667
gt; 2.833333333
gt; -1
gt; -0.166666667
gt; 1.5
gt; -1.666666667
gt; -0.5
gt; 3.166666667
gt; -2.833333333
gt; 1.5
gt; 1.166666667
gt; 1.333333333
gt; -2.666666667
gt; 0.5
gt; 0.166666667
gt; 0
gt; -1.666666667
gt; 0.666666667
gt; 0.166666667
gt; 0.333333333
gt; 0.833333333
gt; 0
gt; 2
gt; -2.666666667
gt; 1
gt; -0.666666667
gt; 1.333333333
gt; -2
gt; 1
gt;
gt;
gt; quot;Harlan Grovequot; wrote:
gt;
gt;gt; Rothman wrote...
gt;gt; gt;quot;Harlan Grovequot; wrote:
gt;gt; ....
gt;gt; gt;gt;You could continue with the brute force approach for subsequent modes,
gt;gt; gt;gt;but
gt;gt; gt;gt;there's a more elegant way to do this. If the modes would be recorded
gt;gt; gt;gt;in
gt;gt; gt;gt;column L beginning in cell L1, use the following formulas.
gt;gt; gt;gt;
gt;gt; gt;gt;L1:
gt;gt; gt;gt;=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),quot;quot;)
gt;gt; gt;gt;
gt;gt; gt;gt;L2 [array formula]:
gt;gt; gt;gt;=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt;gt; gt;gt;MODE($A$1:$J$10))))gt;=ROWS(L$1:L2),MODE(IF(COUNTI F(L$1:L1,$A$1:$J$10)=0,
gt;gt; gt;gt;$A$1:$J$10)),quot;quot;)
gt;gt; gt;gt;
gt;gt; gt;gt;Fill L2 down until it returns quot;quot;.
gt;gt; gt;
gt;gt; gt;That formula doesn't like zeroes. If I have meaningful zeroes in my
gt;gt; gt;data,
gt;gt; gt;the formula throws them into the ranked list in the wrong place (i.e.
gt;gt; gt;the
gt;gt; gt;zeroes are less frequent than those further down the list).
gt;gt;
gt;gt; I don't know what you did wrong, but with the following revised sample
gt;gt; data in A1:J10,
gt;gt;
gt;gt; 7 1 9 0 0 7 0 5 1 9
gt;gt; 4 7 5 5 3 2 5 4 3 7
gt;gt; 2 5 9 9 7 6 8 2 1 5
gt;gt; 1 0 7 6 6 2 3 3 0 9
gt;gt; 7 8 6 1 0 5 1 9 7 3
gt;gt; 3 5 8 7 2 0 5 8 0 0
gt;gt; 8 1 8 5 1 3 9 1 7 4
gt;gt; 5 9 0 0 1 6 6 8 5 7
gt;gt; 7 2 3 0 1 1 2 0 5 5
gt;gt; 6 7 6 7 1 1 8 0 3 4
gt;gt;
gt;gt; and the following formulas
gt;gt;
gt;gt; L1:
gt;gt; =IF(COUNT(MODE($A$1:$J$10)),MODE($A$1:$J$10),quot;quot;)
gt;gt;
gt;gt; L2 [array formula]:
gt;gt; =IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt;gt; MODE($A$1:$J$10))))gt;ROWS(L$1:L1),MODE(IF(COUNTIF(L $1:L1,$A$1:$J$10)=0,
gt;gt; $A$1:$J$10)),quot;quot;)
gt;gt;
gt;gt; L2 filled down into L3:L5, then L1:L5 evaluates to {7;1;0;5;quot;quot;}. The
gt;gt; formula returns zero when zero is a mode. So what did you do wrong? Are
gt;gt; your values unrounded formula results that just appear to be zero but
gt;gt; are actually very small nonzero (and unequal) values?
gt;gt;
gt;gt;
Okay, this is strange.

When I paste the numbers from my post, yes the mode is correct.

HOWEVER, when I paste the numbers from my data (which is derived from a very
simple formula (=cell-cell), but the zeros are zeros) as values in a separate
worksheet (i.e. not formulas), the problem with the mode switching still
exists!

quot;Mike Middletonquot; wrote:

gt; Rothman -
gt;
gt; I pasted the 75 numbers into Excel, and the MODE function
gt; returns -1.666666667.
gt;
gt; I may have missed parts of this thread, but I suggest you answer an
gt; extension of one of the original questions: quot;Are your values unrounded
gt; formula results that just appear to be zero but are actually very small
gt; nonzero (and unequal) values?quot;
gt;
gt; That is, are some of your 75 numbers the result of formulas?
gt;
gt; If so, that could explain the differences between the 75
gt; ten-significant-digit numbers in your message and the 75
gt; fifteen-significant-digit results of formulas on your worksheet.
gt;
gt; - Mike
gt; www.mikemiddleton.com
gt;
gt; quot;Rothmanquot; gt; wrote in message
gt; ...
gt; gt; Ah, but try this one on for size. Below are 75 numbers in a single
gt; gt; column.
gt; gt; Excel tells me the number 1 is the mode. Using countif, I get a frequency
gt; gt; of
gt; gt; 4.
gt; gt;
gt; gt; However, your formula reveals the true mode, albeit in a strange place.
gt; gt; -1.66666667 shows up 7 times in this set of numbers -- it should be the
gt; gt; mode!
gt; gt; And yet Excel insists on the number 1 when I use the simple Mode function
gt; gt; (=MODE()).
gt; gt;
gt; gt; And here's something else that is goofy: =Mode() gives me the correct
gt; gt; number when the numbers are organized lowest to highest, but when they're
gt; gt; in
gt; gt; the order I need them to be in, it resorts back to the wrong mode (#1).
gt; gt;
gt; gt; There's something amiss here.
gt; gt;
gt; gt; 0.833333333
gt; gt; 0.333333333
gt; gt; -1.833333333
gt; gt; -0.833333333
gt; gt; 3
gt; gt; -0.333333333
gt; gt; -2
gt; gt; 1
gt; gt; -1.666666667
gt; gt; 2
gt; gt; -1.166666667
gt; gt; 1.5
gt; gt; 0
gt; gt; -1
gt; gt; -1.333333333
gt; gt; 4.166666667
gt; gt; -3
gt; gt; -0.166666667
gt; gt; -2.5
gt; gt; 2.166666667
gt; gt; 2
gt; gt; -2.5
gt; gt; 0.5
gt; gt; 1.333333333
gt; gt; 0.166666667
gt; gt; -0.833333333
gt; gt; 0.5
gt; gt; -1.666666667
gt; gt; 2.333333333
gt; gt; 1
gt; gt; -1.5
gt; gt; -1
gt; gt; -1.166666667
gt; gt; 2.166666667
gt; gt; -0.833333333
gt; gt; 0.833333333
gt; gt; 1.666666667
gt; gt; -1.666666667
gt; gt; 0.666666667
gt; gt; -1.666666667
gt; gt; 2
gt; gt; -1.833333333
gt; gt; -0.166666667
gt; gt; 0.333333333
gt; gt; -0.333333333
gt; gt; -0.666666667
gt; gt; -1.666666667
gt; gt; 2.833333333
gt; gt; -1
gt; gt; -0.166666667
gt; gt; 1.5
gt; gt; -1.666666667
gt; gt; -0.5
gt; gt; 3.166666667
gt; gt; -2.833333333
gt; gt; 1.5
gt; gt; 1.166666667
gt; gt; 1.333333333
gt; gt; -2.666666667
gt; gt; 0.5
gt; gt; 0.166666667
gt; gt; 0
gt; gt; -1.666666667
gt; gt; 0.666666667
gt; gt; 0.166666667
gt; gt; 0.333333333
gt; gt; 0.833333333
gt; gt; 0
gt; gt; 2
gt; gt; -2.666666667
gt; gt; 1
gt; gt; -0.666666667
gt; gt; 1.333333333
gt; gt; -2
gt; gt; 1
gt; gt;
gt; gt;
gt; gt; quot;Harlan Grovequot; wrote:
gt; gt;
gt; gt;gt; Rothman wrote...
gt; gt;gt; gt;quot;Harlan Grovequot; wrote:
gt; gt;gt; ....
gt; gt;gt; gt;gt;You could continue with the brute force approach for subsequent modes,
gt; gt;gt; gt;gt;but
gt; gt;gt; gt;gt;there's a more elegant way to do this. If the modes would be recorded
gt; gt;gt; gt;gt;in
gt; gt;gt; gt;gt;column L beginning in cell L1, use the following formulas.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;L1:
gt; gt;gt; gt;gt;=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),quot;quot;)
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;L2 [array formula]:
gt; gt;gt; gt;gt;=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt; gt;gt; gt;gt;MODE($A$1:$J$10))))gt;=ROWS(L$1:L2),MODE(IF(COUNTI F(L$1:L1,$A$1:$J$10)=0,
gt; gt;gt; gt;gt;$A$1:$J$10)),quot;quot;)
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;Fill L2 down until it returns quot;quot;.
gt; gt;gt; gt;
gt; gt;gt; gt;That formula doesn't like zeroes. If I have meaningful zeroes in my
gt; gt;gt; gt;data,
gt; gt;gt; gt;the formula throws them into the ranked list in the wrong place (i.e.
gt; gt;gt; gt;the
gt; gt;gt; gt;zeroes are less frequent than those further down the list).
gt; gt;gt;
gt; gt;gt; I don't know what you did wrong, but with the following revised sample
gt; gt;gt; data in A1:J10,
gt; gt;gt;
gt; gt;gt; 7 1 9 0 0 7 0 5 1 9
gt; gt;gt; 4 7 5 5 3 2 5 4 3 7
gt; gt;gt; 2 5 9 9 7 6 8 2 1 5
gt; gt;gt; 1 0 7 6 6 2 3 3 0 9
gt; gt;gt; 7 8 6 1 0 5 1 9 7 3
gt; gt;gt; 3 5 8 7 2 0 5 8 0 0
gt; gt;gt; 8 1 8 5 1 3 9 1 7 4
gt; gt;gt; 5 9 0 0 1 6 6 8 5 7
gt; gt;gt; 7 2 3 0 1 1 2 0 5 5
gt; gt;gt; 6 7 6 7 1 1 8 0 3 4
gt; gt;gt;
gt; gt;gt; and the following formulas
gt; gt;gt;
gt; gt;gt; L1:
gt; gt;gt; =IF(COUNT(MODE($A$1:$J$10)),MODE($A$1:$J$10),quot;quot;)
gt; gt;gt;
gt; gt;gt; L2 [array formula]:
gt; gt;gt; =IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt; gt;gt; MODE($A$1:$J$10))))gt;ROWS(L$1:L1),MODE(IF(COUNTIF(L $1:L1,$A$1:$J$10)=0,
gt; gt;gt; $A$1:$J$10)),quot;quot;)
gt; gt;gt;
gt; gt;gt; L2 filled down into L3:L5, then L1:L5 evaluates to {7;1;0;5;quot;quot;}. The
gt; gt;gt; formula returns zero when zero is a mode. So what did you do wrong? Are
gt; gt;gt; your values unrounded formula results that just appear to be zero but
gt; gt;gt; are actually very small nonzero (and unequal) values?
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

There must be something about it not liking having to subtract irrational
decimals (i.e. =cell-cell)

I've found another formula to get the same numbers that doesn't trip up the
mode function (just from the nature of my data, there's a simple average that
could be taken instead).

Still, this is frustrating.

quot;Rothmanquot; wrote:

gt; Okay, this is strange.
gt;
gt; When I paste the numbers from my post, yes the mode is correct.
gt;
gt; HOWEVER, when I paste the numbers from my data (which is derived from a very
gt; simple formula (=cell-cell), but the zeros are zeros) as values in a separate
gt; worksheet (i.e. not formulas), the problem with the mode switching still
gt; exists!
gt;
gt; quot;Mike Middletonquot; wrote:
gt;
gt; gt; Rothman -
gt; gt;
gt; gt; I pasted the 75 numbers into Excel, and the MODE function
gt; gt; returns -1.666666667.
gt; gt;
gt; gt; I may have missed parts of this thread, but I suggest you answer an
gt; gt; extension of one of the original questions: quot;Are your values unrounded
gt; gt; formula results that just appear to be zero but are actually very small
gt; gt; nonzero (and unequal) values?quot;
gt; gt;
gt; gt; That is, are some of your 75 numbers the result of formulas?
gt; gt;
gt; gt; If so, that could explain the differences between the 75
gt; gt; ten-significant-digit numbers in your message and the 75
gt; gt; fifteen-significant-digit results of formulas on your worksheet.
gt; gt;
gt; gt; - Mike
gt; gt; www.mikemiddleton.com
gt; gt;
gt; gt; quot;Rothmanquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Ah, but try this one on for size. Below are 75 numbers in a single
gt; gt; gt; column.
gt; gt; gt; Excel tells me the number 1 is the mode. Using countif, I get a frequency
gt; gt; gt; of
gt; gt; gt; 4.
gt; gt; gt;
gt; gt; gt; However, your formula reveals the true mode, albeit in a strange place.
gt; gt; gt; -1.66666667 shows up 7 times in this set of numbers -- it should be the
gt; gt; gt; mode!
gt; gt; gt; And yet Excel insists on the number 1 when I use the simple Mode function
gt; gt; gt; (=MODE()).
gt; gt; gt;
gt; gt; gt; And here's something else that is goofy: =Mode() gives me the correct
gt; gt; gt; number when the numbers are organized lowest to highest, but when they're
gt; gt; gt; in
gt; gt; gt; the order I need them to be in, it resorts back to the wrong mode (#1).
gt; gt; gt;
gt; gt; gt; There's something amiss here.
gt; gt; gt;
gt; gt; gt; 0.833333333
gt; gt; gt; 0.333333333
gt; gt; gt; -1.833333333
gt; gt; gt; -0.833333333
gt; gt; gt; 3
gt; gt; gt; -0.333333333
gt; gt; gt; -2
gt; gt; gt; 1
gt; gt; gt; -1.666666667
gt; gt; gt; 2
gt; gt; gt; -1.166666667
gt; gt; gt; 1.5
gt; gt; gt; 0
gt; gt; gt; -1
gt; gt; gt; -1.333333333
gt; gt; gt; 4.166666667
gt; gt; gt; -3
gt; gt; gt; -0.166666667
gt; gt; gt; -2.5
gt; gt; gt; 2.166666667
gt; gt; gt; 2
gt; gt; gt; -2.5
gt; gt; gt; 0.5
gt; gt; gt; 1.333333333
gt; gt; gt; 0.166666667
gt; gt; gt; -0.833333333
gt; gt; gt; 0.5
gt; gt; gt; -1.666666667
gt; gt; gt; 2.333333333
gt; gt; gt; 1
gt; gt; gt; -1.5
gt; gt; gt; -1
gt; gt; gt; -1.166666667
gt; gt; gt; 2.166666667
gt; gt; gt; -0.833333333
gt; gt; gt; 0.833333333
gt; gt; gt; 1.666666667
gt; gt; gt; -1.666666667
gt; gt; gt; 0.666666667
gt; gt; gt; -1.666666667
gt; gt; gt; 2
gt; gt; gt; -1.833333333
gt; gt; gt; -0.166666667
gt; gt; gt; 0.333333333
gt; gt; gt; -0.333333333
gt; gt; gt; -0.666666667
gt; gt; gt; -1.666666667
gt; gt; gt; 2.833333333
gt; gt; gt; -1
gt; gt; gt; -0.166666667
gt; gt; gt; 1.5
gt; gt; gt; -1.666666667
gt; gt; gt; -0.5
gt; gt; gt; 3.166666667
gt; gt; gt; -2.833333333
gt; gt; gt; 1.5
gt; gt; gt; 1.166666667
gt; gt; gt; 1.333333333
gt; gt; gt; -2.666666667
gt; gt; gt; 0.5
gt; gt; gt; 0.166666667
gt; gt; gt; 0
gt; gt; gt; -1.666666667
gt; gt; gt; 0.666666667
gt; gt; gt; 0.166666667
gt; gt; gt; 0.333333333
gt; gt; gt; 0.833333333
gt; gt; gt; 0
gt; gt; gt; 2
gt; gt; gt; -2.666666667
gt; gt; gt; 1
gt; gt; gt; -0.666666667
gt; gt; gt; 1.333333333
gt; gt; gt; -2
gt; gt; gt; 1
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Harlan Grovequot; wrote:
gt; gt; gt;
gt; gt; gt;gt; Rothman wrote...
gt; gt; gt;gt; gt;quot;Harlan Grovequot; wrote:
gt; gt; gt;gt; ....
gt; gt; gt;gt; gt;gt;You could continue with the brute force approach for subsequent modes,
gt; gt; gt;gt; gt;gt;but
gt; gt; gt;gt; gt;gt;there's a more elegant way to do this. If the modes would be recorded
gt; gt; gt;gt; gt;gt;in
gt; gt; gt;gt; gt;gt;column L beginning in cell L1, use the following formulas.
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt;L1:
gt; gt; gt;gt; gt;gt;=IF(COUNT(MODE(A1:J10)),MODE(A1:J10),quot;quot;)
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt;L2 [array formula]:
gt; gt; gt;gt; gt;gt;=IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt; gt; gt;gt; gt;gt;MODE($A$1:$J$10))))gt;=ROWS(L$1:L2),MODE(IF(COUNTI F(L$1:L1,$A$1:$J$10)=0,
gt; gt; gt;gt; gt;gt;$A$1:$J$10)),quot;quot;)
gt; gt; gt;gt; gt;gt;
gt; gt; gt;gt; gt;gt;Fill L2 down until it returns quot;quot;.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt;That formula doesn't like zeroes. If I have meaningful zeroes in my
gt; gt; gt;gt; gt;data,
gt; gt; gt;gt; gt;the formula throws them into the ranked list in the wrong place (i.e.
gt; gt; gt;gt; gt;the
gt; gt; gt;gt; gt;zeroes are less frequent than those further down the list).
gt; gt; gt;gt;
gt; gt; gt;gt; I don't know what you did wrong, but with the following revised sample
gt; gt; gt;gt; data in A1:J10,
gt; gt; gt;gt;
gt; gt; gt;gt; 7 1 9 0 0 7 0 5 1 9
gt; gt; gt;gt; 4 7 5 5 3 2 5 4 3 7
gt; gt; gt;gt; 2 5 9 9 7 6 8 2 1 5
gt; gt; gt;gt; 1 0 7 6 6 2 3 3 0 9
gt; gt; gt;gt; 7 8 6 1 0 5 1 9 7 3
gt; gt; gt;gt; 3 5 8 7 2 0 5 8 0 0
gt; gt; gt;gt; 8 1 8 5 1 3 9 1 7 4
gt; gt; gt;gt; 5 9 0 0 1 6 6 8 5 7
gt; gt; gt;gt; 7 2 3 0 1 1 2 0 5 5
gt; gt; gt;gt; 6 7 6 7 1 1 8 0 3 4
gt; gt; gt;gt;
gt; gt; gt;gt; and the following formulas
gt; gt; gt;gt;
gt; gt; gt;gt; L1:
gt; gt; gt;gt; =IF(COUNT(MODE($A$1:$J$10)),MODE($A$1:$J$10),quot;quot;)
gt; gt; gt;gt;
gt; gt; gt;gt; L2 [array formula]:
gt; gt; gt;gt; =IF(SUMPRODUCT(--(FREQUENCY($A$1:$J$10,$A$1:$J$10)=COUNTIF($A$1:$J$ 10,
gt; gt; gt;gt; MODE($A$1:$J$10))))gt;ROWS(L$1:L1),MODE(IF(COUNTIF(L $1:L1,$A$1:$J$10)=0,
gt; gt; gt;gt; $A$1:$J$10)),quot;quot;)
gt; gt; gt;gt;
gt; gt; gt;gt; L2 filled down into L3:L5, then L1:L5 evaluates to {7;1;0;5;quot;quot;}. The
gt; gt; gt;gt; formula returns zero when zero is a mode. So what did you do wrong? Are
gt; gt; gt;gt; your values unrounded formula results that just appear to be zero but
gt; gt; gt;gt; are actually very small nonzero (and unequal) values?
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt;
gt; gt;
gt; gt;

Rothman wrote...
gt;There must be something about it not liking having to subtract irrational
gt;decimals (i.e. =cell-cell)
gt;
gt;I've found another formula to get the same numbers that doesn't trip up the
gt;mode function (just from the nature of my data, there's a simple average that
gt;could be taken instead).
gt;
gt;Still, this is frustrating.
....

Formulas like =X99-Y100 are a very good example of formulas that may
appear to produce the same results but are just slightly off after
floating point rounding error. If you believe two numbers should be the
same at, say, 3 decimal places, then you need to round them to 3
decimal places. Figuring that Excel (or any other software that uses
finite precision floating point math) will do the right thing and that
you can trust what it displays is very fertile ground for floating
point bugs.