| View previous topic :: View next topic |
| 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:
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 |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
|
|
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
|
