Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Arithmetical Mode Value for Filtered cells in Multiple Adjacent columns | Excel Worksheet Functions | |||
referencing cells that return blank results | Excel Worksheet Functions | |||
Return Multiple Results with Lookup | Excel Worksheet Functions |