+ Reply to Thread
Results 1 to 2 of 2

Grouping a Set of Numbers based on Difference between Groups

Hybrid View

  1. #1
    Registered User
    Join Date
    02-05-2013
    Location
    Minneapolis
    MS-Off Ver
    Excel 2010
    Posts
    1

    Grouping a Set of Numbers based on Difference between Groups

    I have a series of number like:
    185.5
    184.6
    176.7
    176.6
    176.0
    173.5
    172.0
    170.4
    170.4
    159.0
    150.0

    I want an algorithm that will create a set of cut off values that groups the numbers where the difference between cut values is 2 to 4 with a maximum of 6 cut off values.

    So the above numbers would be grouped like:

    Group 1 - Numbers greater than 181
    185.5
    184.6

    Group 2 - Numbers greater than 177, since difference between cut values is 4 and next grouping is at 176.7
    No numbers

    Group 3 - Numbers greater than 174
    176.7
    176.6
    176.0

    Group 4 - Numbers greater than 169, make 169 instead of 170 because no number in range 166 to 170
    173.5
    172.0
    170.4
    170.4

    Group 5 - Numbers greater than 164, make 164 instead of 170 because no number in range 165 to 169
    No numbers

    Group 6 - All other Numbers less than 159
    158.0
    150.0

    These numbers are speed numbers for a group of horses, so I am trying to group them based on the fact a difference of 2 to 4 points is significant.

    If anyone knows the Mathematical name for this problem that would help to.

    Thanks for any help.
    Greg

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: Grouping a Set of Numbers based on Difference between Groups

    Hmm, your logic and actual grouping is in conflict...

    Basically, what you are trying to do is set up Bin with variable container range (Frequency Distribution).

    Fuzzy logic like what you have done in actual grouping is bit tricky/difficult to do with VBA. Unless the logic can be expressed mathematically.
    If it's fixed range which is set at start of operation (say 2, 3, 4 or some other number), it will be much simpler.

    I'd suggest you upload few different data set and what grouping you'd like to see for each.

    As I'd imagine grouping will change drastically depending on sample distribution range.

    Edit: You might find following link helpful (using fixed calculated Bin size)
    Dynamic Histogram/Frequency Distribution Chart
    https://www.excelcampus.com/charts/dynamic-histogram/
    Last edited by CK76; 05-17-2017 at 01:11 PM.
    "Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
    ― Robert A. Heinlein

+ 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. match numbers based on conditions and show the difference
    By coolmob in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-28-2017, 02:05 PM
  2. Repeating calc based on difference between two numbers
    By rfcomm2k in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-05-2016, 05:40 PM
  3. [SOLVED] Grouping Dates into Days and times into Groups
    By mgmerv in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-28-2015, 05:49 AM
  4. Replies: 9
    Last Post: 12-22-2013, 09:29 PM
  5. Insert numbers of based upon difference of two numbers
    By excellentlearner in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-08-2012, 05:54 PM
  6. Grouping and Ranking within Groups
    By penfold in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-21-2010, 01:05 PM
  7. Grouping and grouping within groups
    By j-giesler in forum Excel General
    Replies: 1
    Last Post: 01-31-2006, 04:35 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