Hi all

I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple"

I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column.

Regards to all

Reppy

On Wed, 20 Jun 2012 19:59:15 -0700 (PDT), Reppy wrote:

Hi all

I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple"

I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column.

Regards to all

Reppy

If Rng is the range containing your strings, then:

This formula must be **array-entered**:

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

On Thursday, June 21, 2012 11:27:31 AM UTC+8, Ron Rosenfeld wrote:
On Wed, 20 Jun 2012 19:59:15 -0700 (PDT), Reppy wrote:

Hi all

I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple"

I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column.

Regards to all

Reppy

If Rng is the range containing your strings, then:

This formula must be **array-entered**:

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

Hello Ron

I must be missing something as when i follow your formula i get the message of too few arguments. In your formula you use 'rng' twice in brackets. I actually only have one column to apply formula to. e.g. P23:p300 where all responses are from a drop down list. There are five choices and i m looking for the most frequent response. Sorry to be a bother but can you tell me what i am missing?

Cheers

Reppy

H

On Thursday, June 21, 2012 12:12:34 PM UTC+8, Reppy wrote:
On Thursday, June 21, 2012 11:27:31 AM UTC+8, Ron Rosenfeld wrote:
On Wed, 20 Jun 2012 19:59:15 -0700 (PDT), Reppy wrote:

Hi all

I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple"

I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column.

Regards to all

Reppy

If Rng is the range containing your strings, then:

This formula must be **array-entered**:

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

Hello Ron

I must be missing something as when i follow your formula i get the message of too few arguments. In your formula you use 'rng' twice in brackets. I actually only have one column to apply formula to. e.g. P23:p300 where all responses are from a drop down list. There are five choices and i m looking for the most frequent response. Sorry to be a bother but can you tell me what i am missing?

Cheers

Reppy

Hello Ron

I have worked it out and it works great. thanks heaps.

Reppy

On Thu, 21 Jun 2012 02:30:20 -0700 (PDT), Reppy wrote:

Hello Ron

I have worked it out and it works great. thanks heaps.

Reppy

Glad to help. Thanks for the feedback.

Out of curiousity, what was the problem with the first try. I would have guessed a formula typo. And yes, Rng needed to be used multiple times in the formula.

On Thursday, June 21, 2012 6:16:12 PM UTC+8, Ron Rosenfeld wrote:
On Thu, 21 Jun 2012 02:30:20 -0700 (PDT), Reppy wrote:

Hello Ron

I have worked it out and it works great. thanks heaps.

Reppy

Glad to help. Thanks for the feedback.

Out of curiousity, what was the problem with the first try. I would have guessed a formula typo. And yes, Rng needed to be used multiple times in the formula.

Yep Ron - a typo. Sometimes i am sure that the fingers have a mind of there own. It was not till i left and returned later and saw the typo.

Cheers and thanks again, it will help me a great deal.

On Thursday, June 21, 2012 6:16:12 PM UTC+8, Ron Rosenfeld wrote:
On Thu, 21 Jun 2012 02:30:20 -0700 (PDT), Reppy wrote:

Hello Ron

I have worked it out and it works great. thanks heaps.

Reppy

Glad to help. Thanks for the feedback.

Out of curiousity, what was the problem with the first try. I would have guessed a formula typo. And yes, Rng needed to be used multiple times in the formula.

Ron

I have one more question - can i add more to that formula so that it will ignore blank cells. By this i mean i want to apply the formula so that it will cover cells that are yet to be entered with data. For example i have the formula to cover A1:A100 but to date i only have date up to A50. If not possible then i will amend formula as I go.

Cheers

Reppy

On Wednesday, June 20, 2012 9:59:15 PM UTC-5, Reppy wrote:
Hi all

I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple"

I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column.

Regards to all

Reppy

modify for column instead of row and enter using CSE Ctrl Shift Enter

=INDEX(B2:K2,MATCH(COUNTIF(B2:K2,B2:K2), COUNTIF(B2:K2,B2:K2),0)) & " ("&COUNTIF(B2:K2,B2:K2)&" times)"

or

=INDEX(D2:D2500,MATCH(MAX(COUNTIF(D2:D2500,D2:D250 0)),COUNTIF(D2:D2500,D2:D2500),0))

On Thursday, June 21, 2012 8:27:40 PM UTC+8, Don Guillett wrote:
On Wednesday, June 20, 2012 9:59:15 PM UTC-5, Reppy wrote:
Hi all

I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple"

I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column.

Regards to all

Reppy

modify for column instead of row and enter using CSE Ctrl Shift Enter

=INDEX(B2:K2,MATCH(COUNTIF(B2:K2,B2:K2), COUNTIF(B2:K2,B2:K2),0)) & " ("&COUNTIF(B2:K2,B2:K2)&" times)"

or

=INDEX(D2:D2500,MATCH(MAX(COUNTIF(D2:D2500,D2:D250 0)),COUNTIF(D2:D2500,D2:D2500),0))

Hello Don

Thanks heaps that worked great. Yourself and Ron are legends. Thanks guys this has been of immense help!!

Regards

Reppy