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

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

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

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

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

On Jul 30, 3:51*pm, matt3542
wrote:
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- Hide quoted text -

- Show quoted text -

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

On Jul 30, 4:18*pm, Pete_UK wrote:
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

On Jul 30, 3:51*pm, matt3542
wrote:



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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

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

"Pete_UK" wrote:

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

On Jul 30, 3:51 pm, matt3542
wrote:
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- Hide quoted text -

- Show quoted text -

thanks again, appreciated

"Pete_UK" wrote:

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

On Jul 30, 4:18 pm, Pete_UK wrote:
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

On Jul 30, 3:51 pm, matt3542
wrote:



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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

Pete

On Jul 30, 5:18*pm, matt3542
wrote:
Hi Pete, thanks for spending the time helping out, that worked perfectly,
many thanks, Matt

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)


--
Biff
Microsoft Excel MVP


"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

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

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

Cheers
Matt

"T. Valko" wrote:

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)


--
Biff
Microsoft Excel MVP


"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

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"matt3542" wrote in message
...
Many thanks Biff, thats clever stuff, worked just right, I would have
never
of come with that!

Cheers
Matt

"T. Valko" wrote:

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)


--
Biff
Microsoft Excel MVP


"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

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

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

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