Mode Function in Excel 2008

[Ed]

Is there a way that the MODE function or any other function in Excel
2008 can report the modes in multi-modal data? The Problem: In the
following data: 5, 5, 6, 6, 8, and 9, Excel reports that the mode is
5, which is wrong. The correct answer is that this distribution is bi-
modal: the modes are 5 and 6. Microsoft has ignore this flaw in Excel
since the beta version of the first version of Excel in 1984-85. After
25 years, it is time for Microsoft to fix this flaw.

Thanks,
Ed

 

[JE McGimpsey]

Is there a way that the MODE function or any other function in Excel
2008 can report the modes in multi-modal data? The Problem: In the
following data: 5, 5, 6, 6, 8, and 9, Excel reports that the mode is
5, which is wrong. The correct answer is that this distribution is bi-
modal: the modes are 5 and 6. Microsoft has ignore this flaw in Excel
since the beta version of the first version of Excel in 1984-85. After
25 years, it is time for Microsoft to fix this flaw.

After 25 years of models that depend on this behavior, it would be folly
for MS to "fix" anything, as it would break those other workbooks.

How would you propose that XL report a bimodal or multimodal result?

A single cell can contain only one numeric value.

That said, here's an array formula solution that Harlan Grove posted a
couple years ago:

http://groups.google.com/group/microsoft.public.excel.worksheet.functions
/msg/19107f96d4be172b