[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
bugmenot
OOo Enthusiast

Joined: 24 Apr 2006
Posts: 144

Posted: Fri Jul 23, 2010 9:38 am    Post subject: MODE for non-numeric values?

Well, kinda self-explanatory, but let me give an example anyway:
 Code: CF CF LWF CF SS SS

A function like "=MODE(A1:A6)" returns #VALUE! when I want it to return "CF". Is there a different function for string values? I didn't find one...

EDIT because I don't want to create a new thread for everything...
Is it possible to use conditional formatting in a way that I can have several string values in one condition? Let's say I want the conditional formatting to apply for the CF and SS values in the example above, but by using only one condition. How would that work?

Last edited by bugmenot on Thu Mar 17, 2011 11:47 pm; edited 1 time in total
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

Posted: Fri Jul 23, 2010 9:00 pm    Post subject:

You could use a helper column, say column B, with...
 Code: =COUNTIF(\$A\$1:\$A\$6;A1)
in B1 filled down to B6
Then in C1...
 Code: =INDEX(A1:A6;MATCH(MAX(\$B\$1:\$B\$6);\$B\$1:\$B\$6))

Or, an array formula, which must be entered using the Ctrl+Shift+Enter key combination, plus if you need to copy the formula to succeeding cells the Ctrl key must be pressed while dragging the fill handle...
 Code: INDEX(A1:A6;MATCH(MAX(COUNTIF(A1:A6;A1:A6));COUNTIF(A1:A6;A1:A6);0))

For the Conditional formatting, select A1:A6 so that A6 is the active cell, then use "Formula Is" with...
 Code: OR(A6="CF";A6="SS")

as the formula.

Ken Johnson
_________________