Saturday, July 14, 2012

yogi_Compute Mode Of Items From A Column Of Text Entries

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #627   Jul 14, 2012     www.energyefficientbuild.com.


user somewhereinasia2 said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/pQLqOBnyvQc)
Is there a way to manipulate MODE function for a string of text when there are actually two modes?

I have a column that shows data like this:
Red
Red
Green
Red
Blue
Green
I can use this formula to find the mode (red): 
=INDEX(I2:I6,MODE(MATCH(I2:I6,I2:I6,0))) 
If I add blank cells to the column, then I can use this:
=INDEX(K2:K13,MODE(IF(K2:K13<>"";MATCH(K2:K13,K2:K13,0);"")))
But if there are actually two modes (Red and Green) like this:
Red
Red
Green
Green
Blue
Green
...then the formula will show the first mode that it finds (red).  I want the formula to tell me if there is more than one mode.  So can I use an IF function to do something like this:
IF ( K2:13 <> The mode from the above formula, then find the mode of those cells, otherwise show this text: "Enter Manually")
Anyone know of a solution to this?  I know there is a way to do it by adding more columns and using countif, but I need everything to be done in one formula in one cell.
Thanks!
---------------------------------------------------------------------------------------------
following is a solution to the problem ... I am not quite sure if this is exactly what the user wants -- however this will get us started and if the user wants something else adjustments can be made to meet user's requirement