OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

MODE for non-numeric values?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
bugmenot
OOo Enthusiast
OOo Enthusiast


Joined: 24 Apr 2006
Posts: 144

PostPosted: Fri Jul 23, 2010 9:38 am    Post subject: MODE for non-numeric values? Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
ken johnson
Super User
Super User


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

PostPosted: Fri Jul 23, 2010 9:00 pm    Post subject: Reply with quote

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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group