Determining the text mode from a variety of data types

[matt3542]

Dear Forum,

I would be very grateful if anyone can provide a solution to the following
problem;

I have a dataset as per the example below that consists of text and numbers.
I am trying to determine the mode based on the text entries and not the
number entries. In the example below the entry "ear infection" would
represent the text mode as it appears the most amount of times. The number
values represent sub totals.

Ear infection
Ear infection
Sinusitis
Ear infection
D&V
5
D&V
Rash on leg
Migraine
3


Many Thanks
Matt

 

[Pete_UK]

So what is it that you want to end up with?

Pete

 

[matt3542]

Hi Pete, ideally the value returned would be "Ear Infection" as this is the
text string that appears the most.

Thanks
Matt

 

[RagDyeR]

Try this - works for text and/or numbers:

=INDEX(A1:A10,MODE(MATCH(A1:A10&"",A1:A10&"",0)))

This formula counts empty cells as zero, so if you sized the range where
empty rows outnumbered those with values, you'd get a 0 return.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi Pete, ideally the value returned would be "Ear Infection" as this is the
text string that appears the most.

Thanks
Matt

 

[Pete_UK]

Ah, that kind of mode !!

One way (off the top of my head). Copy that column to another sheet,
ensure you have a header in A1 (insert a new row at the top if
necessary and type a word in A1), then sort the data, so that your
numbers are bunched at the top (and you can easily delete the rows
with them in) such that you are left just with the text entries.
Highlight all the data, including the header, and click on Data |
Filter | Advanced Filter. Your range should already be identified, but
you want to choose Unique records only and Copy to another location -
put in C1 as the destination and click OK. Now you can delete columns
A and B, leaving you with a unique list in alphabetical order in
column A. In B2 you can enter this formula:

=COUNTIF(Sheet1!A:A,A2)

then copy this down, to give you a count of each of the text values in
the other sheet. From here you can easily use the MODE function to
find the most common.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, at the end you would use MAX to find the largest, and then use
INDEX/MATCH to return the string, but I see tht RD has given you a
better solution.

Pete

 

[matt3542]

Hi RagDyeR,

Thanks for the reply. Unfortunately it is returning 0 as the result -
apologies I should of added that there are more empty cells than completed
cells, i.e

a1 blank
a2 blank
a3 blank
a4 3
a5 cat
a6 cat
a7 blank

Ideally the expected text string mode would be "cat" but as per the above it
is returning 0. Please can you advise if there is a way around this?

Thanks
Matt

 

[matt3542]

Hi Pete, thanks for spending the time helping out, that worked perfectly,
many thanks, Matt

 

[matt3542]

thanks again, appreciated

Sorry, at the end you would use MAX to find the largest, and then use
INDEX/MATCH to return the string, but I see tht RD has given you a
better solution.

Pete

 

[Pete_UK]

You're welcome, Matt - thanks for feeding back (both times !!).

Pete

 

[T. Valko]

Assuming that your "blank" cells are *empty* cells.

Try this array formula** :

=INDEX(rng,MODE(IF(ISTEXT(rng),MATCH(rng,rng,0))))

If there isn't a "mode" then the formula returns #N/A.

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

 

[RagDyer]

Try this *array* formula which will still work for numbers and/or text, and
will by-pass blank cells:

=INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))))
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

 

[matt3542]

Many thanks Biff, thats clever stuff, worked just right, I would have never
of come with that!

Cheers
Matt

 

[matt3542]

Hi RagDyer, thanks for taking the time to devise something different but
unfortunately it is now returning the mode as a numeric value. Biff has
offered an answer which seems to work ok, cheers, matt

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[RagDyeR]

There's nothing wrong with the formula!

It *will* return either a number or a text value, whichever is the largest
presence in the referenced array.

Did you copy it right from my post?

 

[T. Valko]

They wanted to *ignore* the numbers and return only the *text* mode.

 

[RagDyer]

Thanks Biff.

The light bulb finally went on!<g>