Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding the 'modal' text value (2002)

    Is it possible to find the most frequently occurring text value in a column using a simple formula? The 'Mode' function doesn't work with text.

    Alternatively, how could I extract this value from a Pivot Table? Thanks, Andy.

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding the 'modal' text value (2002)

    This may be a long way round, but...

    in another column, use a COUNTIF formula to find the number of occurrences of the text in question. Then use MAX to find the maximum number of occurrences. Then you can use a LOOKUP formula to find the corresponding text.

    See attached example.
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding the 'modal' text value (2002)

    I odn't sknow if this is "simple enough" but if your text is in A1:A100 this should work:

    =OFFSET(A1,MIN(IF(COUNTIF($A$1:$A$100,A1:A100)=MAX (COUNTIF($A$1:$A$100,A1:A100)),ROW(A1:A100)))-1,0)

    If there is more than 1 mode it will find the one whose first entry has the lowest row number (ie first seen in the list)

    Steve

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Finding the 'modal' text value (2002)

    Assuming your range is named data, this array-formula will return the most frequent text (or the first if more than one value appears the same number of times):
    =INDEX(data,MATCH(MAX(COUNTIF(data,data)),COUNTIF( data,data),0))
    (press Ctrl+Shift+Enter to enter)
    Or with a pivot table, put the text field into the row field area, then also into the data area.Then right-click the field in the row area, choose Advanced, then select Top 1.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding the 'modal' text value (2002)

    Thank you everyone. All suggestions have been helpful. I particularly like the Pivot Table, and 'Top 1' idea.
    Andy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •