+ Reply to Thread
Results 1 to 7 of 7

Mode (Most Frequency) in Ranges

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,101

    Mode (Most Frequency) in Ranges

    Hello Excel Gurus,

    I have 2 questions that I don't even know if there's a solution to it.

    Given a range of numbers (below), we can see that 0-1 (6x), 1-2 (5x), 2-3 (4x), 3-4 (3x), 4-5 (6x), 5-6 (5x), 6-7 (6x), 7-8 (2x), 8-9 (3x), 9-10 (2x)

    0, 0.1, 0.2, 0.3, 0.4, 1.0,
    1.1, 1.2, 1.5, 1.9, 2.0,
    2.1, 2.2, 2.7, 2.8,
    3.2, 3.5, 3.6,
    4.1, 4.3, 4.5, 4.7, 4.9,5.0,
    5.2, 5.4, 5.6, 5.8, 6.0,
    6.1, 6.2, 6.4, 6.5, 6.8, 6.9,
    7.5,7.9,
    8.1, 8.8, 9.0,
    9.1, 10

    The 1st question is that, How do I spit out the no. of occurance in the given range. Which is shown above. i.e. 0-1 (6x), 1-2 (5x), ... ..., 9-10 (2x). Without the 'x' of course.

    2nd question is, how to tell the user, what's the range that occured most. In our example, it will spits out 0-1, 4-5 and 6-7 as the answer.

    Updated:
    Attached Files Attached Files
    Last edited by dluhut; 04-12-2012 at 04:03 PM.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Mode (Most Frequency) in Ranges

    could you attach this in a sample workbook? just want to make sure i am doing the correct procedure.
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,040

    Re: Mode (Most Frequency) in Ranges

    Maybe this:
    Attached Files Attached Files
    Never use Merged Cells in Excel

  4. #4
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Mode (Most Frequency) in Ranges

    once you have the number of occurances per line you can just use the MODE function, but are all of those values in multipule cells, or in once cell, with comma seperation

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,040

    Re: Mode (Most Frequency) in Ranges

    In 2nd solution is what you asking for but in that approach it's important to write numbers in each row from same range (for example 7.5,7.9, in 7th row).
    In 1st solution that's not important but it define ranges as bigger or equal than 0 and less than 1 (for 1st range).
    That means that 1 is in 2nd range (0-0.99999, 1 - 1.9999, 2-2.99999 etc) as it should be and not in first (0-1, 1.000001-2, 2.000001-3 etc)

  6. #6
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,101

    Re: Mode (Most Frequency) in Ranges

    Very close though zbor. But attached is the spreadsheet that i've done.

    Hopefully you can help out.

    Thanks DGagnon & Zbor!!! Really appreciate it!

    And to answer your question DGagnon, there's no comma or anything. It's a number in each cell, and there can be multiple columns
    Last edited by dluhut; 04-12-2012 at 04:05 PM.

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,040

    Re: Mode (Most Frequency) in Ranges

    so 0-1 and 1-2... where is 1 calculated?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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