+ Reply to Thread
Results 1 to 3 of 3

Most frequently occurred text (month) in a range.

  1. #1
    Registered User
    Join Date
    03-15-2016
    Location
    Baku, Azerbaijan
    MS-Off Ver
    2013
    Posts
    3

    Most frequently occurred text (month) in a range.

    Dear all,

    I have number of month names in A1:A24 (Jan, Feb, Mar, Apr). I need to know which month has occurred most frequently than others. An amazing formula is available to solve the problem written by admin of excelxor. (username)

    =INDEX(A1:A24;MODE(MATCH(A1:A24;A1:A24;0)))

    I just want to understand how this formula works. I can't achieve the same result by writing the parts of the formula. What does Match(A1:A24;A1:A24;0) do here? If it returns 24 results here then why doesn't it work when used separately? (If I type the formula in any given cell it'll return 1). Mode is set to return frequency of a number that most frequently occurs in a range and it doesn't work with text values. How does it work here?

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Most frequently occurred text (month) in a range.

    Take innermost part:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    it returns just 1.
    But place cursor in formula bar and press F9
    you will see an array of numbers.
    These are rows, where given Ax value appeared first time
    so if your data in A1:A24 is like :
    Please Login or Register  to view this content.
    you will see
    something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    in formula bar.
    So you have an array of row numbers, where given value appeared first time
    Mode will take the most frequent row number (in sample case - row 2)
    and index will select from A1:A24 second value, which is "a"
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    03-15-2016
    Location
    Baku, Azerbaijan
    MS-Off Ver
    2013
    Posts
    3

    Re: Most frequently occurred text (month) in a range.

    Thank you for the answer. Match(A1:A15; A1:A15; 0) returns the row of unique values in the range. I'm amazed by its capabilities

    Quote Originally Posted by Kaper View Post
    Take innermost part:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    it returns just 1.
    But place cursor in formula bar and press F9
    you will see an array of numbers.
    These are rows, where given Ax value appeared first time
    so if your data in A1:A24 is like :
    Please Login or Register  to view this content.
    you will see
    something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    in formula bar.
    So you have an array of row numbers, where given value appeared first time
    Mode will take the most frequent row number (in sample case - row 2)
    and index will select from A1:A24 second value, which is "a"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Most Frequently Occuring Text
    By Shanie in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-25-2015, 12:08 PM
  2. Replies: 7
    Last Post: 01-14-2015, 11:38 PM
  3. Find most frequently occurring text string in in a VISIBLE range of cells
    By Odlanier in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-01-2013, 05:27 PM
  4. Return most frequently occurring text in row
    By penfold in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-13-2011, 12:38 PM
  5. Most frequently occurring text
    By Deanomcbeano in forum Excel General
    Replies: 4
    Last Post: 08-12-2009, 08:58 AM
  6. [SOLVED] Extracting the most frequently occuring text from a range
    By Phil in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-09-2006, 08:45 AM
  7. Extracting the most frequently occuring text from a range
    By Phil in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-08-2006, 10:51 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1