+ Reply to Thread
Results 1 to 3 of 3

Sharing out an array of random numbers between a group of a random 2-7 people?

  1. #1
    Registered User
    Join Date
    12-18-2008
    Location
    White Rock
    Posts
    76

    Sharing out an array of random numbers between a group of a random 2-7 people?

    I'm trying to try and kind of evenly share an array of differently sized numbers between a random sized group of people.

    No expectation that the numbers will share out perfectly evenly, and it may even be necessary to ignore the 'biggest' number in the array to allow a more even-ish sharing.

    As a possible example, say the situation is:

    4 people and an array of {1000,600,500,500,450,150,100,100,100,100,100,50,50,50,50,50,20,10}, it might iterate through as:

    person 1 (1000): 1000
    person 2 (600): 600
    person 3 (500): 500
    person 4 (500): 500
    person 4 (950): 500, 450 (add largest number to smallest pile)
    person 3 (650): 500, 150
    person 2 (700): 600, 100
    person 3 (750): 500, 150, 100
    person 2 (800): 600, 100, 100
    person 3 (850): 500, 150, 100, 100
    person 2 (900): 600, 100, 100, 100
    person 3 (900): 500, 150, 100, 100, 50
    person 2 (950): 600, 100, 100, 100, 50
    person 3 (950): 500, 150, 100, 100, 50, 50
    person 4 (1000): 500, 450, 50
    person 2 (970) : 600, 100, 100, 100, 50, 20
    person 3 (960) : 500, 150, 100, 100, 50, 50, 10

  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: Sharing out an array of random numbers between a group of a random 2-7 people?

    Hi,

    As this post is in Formulas & Function section (not im macro/programming section - which would be the most natural), I assume that you would like to have it solved with built in functions. So how about Solver? If it's not visible in Data ribbon, you shall activate this add-in in Options.

    See attached file - assigned person is a changed variable
    sumif is used to sum all assigned numbers to given person. standard deviation of sums (it's minimization) is the goal. And evolutionary engine is used (availabe, if I remember since excel 2010).
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    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: Sharing out an array of random numbers between a group of a random 2-7 people?

    See also solution with formulas. It's composed of 4 groups of columns:
    1) numbers to be assigned
    2) sums assigned to given person in all previous steps (C3 and down/right):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    3) nomination formula - finding who shall get new assignment, because has lowest sum so far (in I3 and down):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    4) number assigned to nominated person in given step (in k3 and down/right):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    It shall be easier to understand if you start analyzing formulas for instance from row 4 (second number to be assigned).

    To change number of people just delete/add columns in group 2 and 4 and check whether "nomination formula" covers all columns of group 2.

    To change number of assigned numbers just add or delete rows with formulas. Note that group 2 has always 1 row more than other groups.
    Attached Files Attached Files
    Last edited by Kaper; 07-24-2018 at 03:50 AM.

+ 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] macro created buttons not working, random numbers not random
    By dareeldill in forum PowerPoint Programing
    Replies: 4
    Last Post: 07-01-2017, 09:16 AM
  2. [SOLVED] Random numbers but each number not eqully random.
    By richhhh in forum Excel General
    Replies: 11
    Last Post: 09-06-2016, 01:16 PM
  3. Generating a list of Random numbers of random length...
    By Growl in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-19-2013, 12:10 PM
  4. Most common numbers in group of random numbers
    By Axxek in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2013, 11:19 AM
  5. Generating a Random Number of Random Numbers
    By Garrus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2010, 09:39 AM
  6. [SOLVED] Non-random numbers generated by excel's data analysis random gener
    By Allie in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-17-2005, 02:05 AM
  7. [SOLVED] Re: Non-random numbers generated by excel's data analysis random gener
    By Harlan Grove in forum Excel General
    Replies: 2
    Last Post: 09-13-2005, 12:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1