MODE() is there a way to return bi-modal or multimodal results?

J

Jdolsak

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
 
H

Harlan Grove

Jdolsak said:
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?
Click to expand...

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 "".
 
R

Rothman

Harlan Grove said:
Jdolsak said:
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?
Click to expand...

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 "".
Click to expand...

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).
 
H

Harlan Grove

Rothman 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(COUNTIF(L$1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

Fill L2 down until it returns "".
Click to expand...

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).
Click to expand...

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?
 
R

Rothman

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 said:
Rothman 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(COUNTIF(L$1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

Fill L2 down until it returns "".
Click to expand...

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).
Click to expand...

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?
Click to expand...
 
R

Rothman

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 said:
Rothman 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(COUNTIF(L$1:L1,$A$1:$J$10)=0,
$A$1:$J$10)),"")

Fill L2 down until it returns "".
Click to expand...

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).
Click to expand...

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?
Click to expand...
 
M

Mike Middleton

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 said:
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 said:
Rothman 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(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).
Click to expand...

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?
Click to expand...
Click to expand...
 
R

Rothman

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!

Mike Middleton said:
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 said:
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 said:
Rothman 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(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).

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?
Click to expand...
Click to expand...
Click to expand...
 
R

Rothman

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.

Rothman said:
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!

Mike Middleton said:
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 said:
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


:

Rothman 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(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).

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?
Click to expand...
Click to expand...
Click to expand...
 
H

Harlan Grove

Rothman wrote...
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.
Click to expand...
....

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