+ Reply to Thread
Results 1 to 9 of 9

Randomise Evenly

  1. #1
    Registered User
    Join Date
    06-30-2016
    Location
    Kettering, UK
    MS-Off Ver
    Office 365
    Posts
    24

    Randomise Evenly

    I'm looking at creating a column which gives me a random number every time data is changed. I know the formular to do this, however I'm just wondering if there is a way that I can potentially make the results as even as possible?

    With the data I've got I will never get an even split as there is never an even number to work with, however I'd like to get it as close as possible if this is acheivable.

    The randomise will only be between 1 & 3 so a limited numberset, but just want the formula to work out so its a random selection.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Randomise Evenly

    I think you need to attach a file (see yellow banner at top) showing what you mean. The terms "random" and "evenly" are a bit contradictory... so I don't know what you want to do.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    06-30-2016
    Location
    Kettering, UK
    MS-Off Ver
    Office 365
    Posts
    24

    Re: Randomise Evenly

    hopefully the attachement is on now.

    Agree it's a little contradictory, just a process I'm trying to do might in the act of fairness
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Randomise Evenly

    Non random random numbers!! Hey ho.

    It is not really possible to do this totally randomly with such a small group of people. If you had a larger group, the distribition would tend towards being "even". Most solutions use either RANDBETWEEN or RAND, which are volatile... and therefore change everytime anything changes on the sheet. If that's a problem, we can address that later. For now, I suggest two options. Blue and purple. I am not going to explain them, until we agree which (if either) does what you want.

    In Blue, the value assignation is still random, but limited. I have limited them using the table. You'll see that somethimes the pattern is pretty even, but not always.

    In purple, I have FIXED (column N) the number of 1s, 2s and 3s. That is absolutely non-random. thereafter the allocation fo the 1s 2s etc to individual staff is entirely random. the helper can, of course, be hidden from view.

    Views??
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-30-2016
    Location
    Kettering, UK
    MS-Off Ver
    Office 365
    Posts
    24

    Re: Randomise Evenly

    The example on shows a handful of names, in the actual workbook the number of names changes on a daily basis & can range between 15 & 38 (usually mid 20's).

    I'd tend to sway towards the purple option.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Randomise Evenly

    Hi Glenn

    EVENLY (haha) I catched this case:

    3x1 & 1x2 &1x3

    Is it that the OP try to avoid?
    Attached Images Attached Images
    Quang PT

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Randomise Evenly

    With number from 1 to 3 and 5 rows, each value has twice occurence:
    {1,1,2,2,3,3}
    next, to test whether the number appears twice, then remove its chance
    Start in E3, then drag down:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Randomise Evenly

    Bebo: Note that I said:

    In Blue, the value assignation is still random, but limited. I have limited them using the table. You'll see that somethimes the pattern is pretty even, but not always.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Randomise Evenly

    Here's a non-volatile version that assigns names based on the choices made by YOU at column I. It ONLY changes when you change the number in B1 (I have put today's date there, as an example).

    The formula in E2 should be copied down as far as needed:
    =IF(D2="","",MOD(($B$1+ROWS(E$2:E2))/PI(),1))

    Likewise in F2(adjust the bits in red to suit you needs):
    =IF(D2="","",LOOKUP(RANK.AVG(E2,$E$2:$E$13),$J$2:$J$4,$H$2:$H$4))

    and assign the desired division between 1, 2 and 3 at column I, to give the total number of staff present.

    Does this do what you want?
    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] Numbers won't randomise for me
    By mhoey8 in forum Excel General
    Replies: 1
    Last Post: 08-09-2019, 12:41 PM
  2. [SOLVED] Randomise items and total their associated cell values
    By Verifyor in forum Excel General
    Replies: 6
    Last Post: 01-03-2018, 10:37 AM
  3. [SOLVED] How to randomise staff for roster
    By kophanz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-12-2016, 04:10 AM
  4. Need to shuffle/randomise order of display
    By simoninparis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-23-2012, 03:46 PM
  5. VBA - randomise 15 question from the pool of 80-90 questions for user to selected
    By tushar@excel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-30-2008, 08:25 AM
  6. randomise numbers in cells
    By msylai in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-29-2008, 06:01 PM
  7. Can I randomise the order of a number of rows?
    By plh in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-11-2005, 08:20 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