Determining the text mode from a variety of data types

M

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
 
M

matt3542

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

Thanks
Matt
 
R

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
 
P

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
 
P

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
 
M

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
 
M

matt3542

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

matt3542

thanks again, appreciated

Pete_UK said:
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
 
T

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)
 
R

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.
 
M

matt3542

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

Cheers
Matt
 
M

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
 
R

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top