Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jdolsak
 
Posts: n/a
Default MODE() is there a way to return bi-modal or multimodal results?

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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default MODE() is there a way to return bi-modal or multimodal results?

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rothman
 
Posts: n/a
Default MODE() is there a way to return bi-modal or multimodal results

"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).
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default MODE() is there a way to return bi-modal or multimodal results

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?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rothman
 
Posts: n/a
Default MODE() is there a way to return bi-modal or multimodal results

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?




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rothman
 
Posts: n/a
Default MODE() is there a way to return bi-modal or multimodal results

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?


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mike Middleton
 
Posts: n/a
Default MODE() is there a way to return bi-modal or multimodal results

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?




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rothman
 
Posts: n/a
Default MODE() is there a way to return bi-modal or multimodal results

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

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?





  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rothman
 
Posts: n/a
Default MODE() is there a way to return bi-modal or multimodal results

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

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

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?





  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default MODE() is there a way to return bi-modal or multimodal results

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.

....

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.



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rothman
 
Posts: n/a
Default MODE() is there a way to return bi-modal or multimodal results

Don't trust Excel. Got it.

Thanks again for your help!

"Harlan Grove" wrote:

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.

....

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Intervals of 1 Numeric value in a Row and Return Count down Column Sam via OfficeKB.com Excel Worksheet Functions 8 October 4th 05 04:37 PM
Count Intervals of 2 Numeric values in same Row and Return Count across Row Sam via OfficeKB.com Excel Worksheet Functions 12 September 24th 05 10:58 PM
Arithmetical Mode Value for Filtered cells in Multiple Adjacent columns Sam via OfficeKB.com Excel Worksheet Functions 4 September 8th 05 12:23 AM
referencing cells that return blank results Suz Excel Worksheet Functions 4 February 21st 05 11:59 PM
Return Multiple Results with Lookup Josh O. Excel Worksheet Functions 1 February 4th 05 09:07 PM


All times are GMT +1. The time now is 11:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"