+ Reply to Thread
Results 1 to 3 of 3

the number that appears most or least

  1. #1
    Registered User
    Join Date
    06-29-2020
    Location
    Vietnam
    MS-Off Ver
    2013
    Posts
    64

    the number that appears most or least

    the number that appears most/least
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    668

    Re: the number that appears most or least

    Most frequent is MODE, so =MODE(B1:B16). With conditions, you could use =AGGREGATE(13,6,B1:b16/(B1:B16>=6)/(B1:B16<=7)), but I prefer avoiding Magic Numbers for readability and maintainability, so there's also array formulas like =MODE(IF((B1:B16>=6)*(B1:B16<=7),B1:B16). Note, however, that there are 4 instances of 5.1 and 5.2, but 5.1 appears first in B1:B16, so Excel returns it as the mode.

    For least occurring number, there are 5 numbers which appear only once. If you want the first of these, try =INDEX(B1:B16,MATCH(1,COUNTIF(B1:B16,B1:B16),0)). To add conditions, use COUNTIFS and add those conditions as additional arguments.

  3. #3
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,065

    Re: the number that appears most or least

    Hi,
    you can use the frequency function for the most option.
    regarding the least - you have multiple answers, since there are several numbers numbers that appear only once and can comply with your condition.
    so what answer are you expecting to see if case of multiple results?

    D3
    Please Login or Register  to view this content.
    E3:
    Please Login or Register  to view this content.
    F3
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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] Count specific number in a table when given number appears in same row (modified)
    By Karnik in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-10-2015, 04:14 AM
  2. Calculating number of times individual number appears in a pivot table
    By snoopy1461 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-15-2014, 08:39 AM
  3. [SOLVED] formula to auto delete number in dropdown list when number appears in another cell #2
    By ddevins in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-28-2014, 08:31 AM
  4. [SOLVED] formula to auto delete number in dropdown list when number appears in another cell
    By ddevins in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-28-2014, 04:54 AM
  5. Replies: 1
    Last Post: 07-18-2013, 10:11 AM
  6. Replies: 3
    Last Post: 09-17-2012, 08:05 AM
  7. Replies: 5
    Last Post: 11-22-2010, 06:31 PM

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