+ Reply to Thread
Results 1 to 4 of 4

Finding the Value with Highest Frequency in a Range

  1. #1
    Registered User
    Join Date
    10-07-2015
    Location
    Tehran - Iran
    MS-Off Ver
    2013
    Posts
    72

    Finding the Value with Highest Frequency in a Range

    I have a range of numbers on column "A" where some of numbers have been repeated several times. My question is:" How can I find the value more than zero with highest frequency in a range?

    I tried below formulas but it does not work truly (sometimes gives me true value and sometimes gives me wrong value). On the other hand, I need the value more than zero with highest frequency.

    =INDEX(B:B, 10000*MOD(MAX(COUNTIF(B2:B16,B2:B16)+(ROW(B2:B16)/10000)),1), 1)

    =INDEX(B2:B20;MODE(MATCH(B2:B20;B2:B20;0)))

    Do you have any solution for my problem?

    Thanks in advance for your cooperation.

    Please see enclosed excel file.
    Attached Files Attached Files

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Finding the Value with Highest Frequency in a Range

    Hi,

    Array formula**:

    =MODE(IF(B2:B20,B2:B20))

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Agawam, MA
    MS-Off Ver
    Microsoft Office 365
    Posts
    843

    Re: Finding the Value with Highest Frequency in a Range

    =INDEX(B2:B20,MODE(IF(B2:B20<>0,MATCH(B2:B20,B2:B20,0))))

    array formula
    use ctrl + shift + enter

  4. #4
    Registered User
    Join Date
    10-07-2015
    Location
    Tehran - Iran
    MS-Off Ver
    2013
    Posts
    72

    Re: Finding the Value with Highest Frequency in a Range

    Thanks.Your formulas as well as works.
    Last edited by soleimani1967; 01-04-2019 at 05:27 PM.

+ 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. [SOLVED] HELP: Finding Person's Highest 6 Scores from Range
    By Tommyslats in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 08-09-2017, 08:34 AM
  2. [SOLVED] Help with finding highest value in a range
    By kochark in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-21-2014, 11:29 AM
  3. Finding next highest number in a range
    By cara13xxi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-20-2013, 04:39 AM
  4. Finding 2nd or 3rd highest value in range
    By jmhultin in forum Excel General
    Replies: 4
    Last Post: 03-11-2013, 06:38 AM
  5. Replies: 4
    Last Post: 10-08-2010, 04:18 AM
  6. Finding highest in a range, and assocated data
    By Bewl in forum Excel General
    Replies: 7
    Last Post: 06-25-2008, 08:01 AM
  7. Finding highest number in a range of cells
    By marcusr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-20-2007, 08:42 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