Thanks Biff.

The light bulb finally went on!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
They wanted to *ignore* the numbers and return only the *text* mode.

--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
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?
--

Regards,

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

"matt3542" wrote in message
...
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

"RagDyer" wrote:

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:A 10,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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------

"matt3542" wrote in message
...
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

"RagDyeR" wrote:

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!
================================================== ===

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

Thanks
Matt

"Pete_UK" wrote:

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

Pete

On Jul 30, 3:31 pm, matt3542
wrote:
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