Excel - Mode for Text

Asked By BryGuy7 on 06-Jun-08 01:22 AM
Hello,

I have a list that includes both blank spaces and text values. How would I
go about finding the 'mode' of the text, while excluding the 0's? For example,

AA

BB


CC

AA

DD


AA

Say this is the list of values I have. Clearly, AA is the most common text.
However, blanks are the most common character. Auto-filter will not work,
since I need this 'mode value' to appear on a separate line, so it can be
linked to another part of the spreadsheet. I use Office 2007. Thanks for the
help!




T. Valko replied on 06-Jun-08 01:46 AM
Try this array formula** :

=INDEX(A1:A20,MODE(IF(A1:A20<>"",MATCH(A1:A20,A1:A20,0))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP