+ Reply to Thread
Results 1 to 6 of 6

RANDBETWEEN Function with conditions

  1. #1
    Registered User
    Join Date
    02-07-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    2

    RANDBETWEEN Function with conditions

    Hi all, if you are able to help with this query that would be brilliant!......

    I am generating a personal tutor spreadsheet to randomly allocate staff tutors to students. I have figured out the basic RANDBETWEEN formula (=INDEX($D$2:$D$21,RANDBETWEEN(1,ROWS($D$2:$D$21)),1)) - where the tutor names are D2:D21 - however, I would like to throw a condition into the mix as not all tutors have the same tutee capacity.

    So, for example in the attached (using anonymous data), there are 200 students and 20 staff members; each staff member has a different capacity on the number of students they can tutor. I can create the random tutor allocation in column B but I'm not sure how to work column E (the tutor capacity) into the formula.

    Any ideas?

    Many thanks
    Nikki
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: RANDBETWEEN Function with conditions

    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    2
    Student
    Draw
    Winner
    Do Not Use
    Staff 01
    Staff 02
    Staff 03
    Staff 04
    Staff 05
    Staff 06
    Staff 07
    Staff 08
    Staff 09
    Staff 10
    3
    10
    10
    12
    12
    10
    8
    10
    8
    10
    9
    4
    Student 001
    142
    Staff 15
    0
    10
    20
    32
    44
    54
    62
    72
    80
    90
    5
    Student 002
    109
    Staff 12
    0
    10
    20
    32
    44
    54
    62
    72
    80
    90
    6
    Student 003
    135
    Staff 14
    0
    10
    20
    32
    44
    54
    62
    72
    80
    90
    7
    Student 004
    58
    Staff 06
    0
    10
    20
    32
    44
    54
    62
    72
    80
    90
    8
    Student 005
    51
    Staff 05
    0
    10
    20
    32
    44
    54
    61
    71
    79
    89
    9
    Student 006
    76
    Staff 08
    0
    10
    20
    32
    44
    53
    60
    70
    78
    88
    10
    Student 007
    44
    Staff 05
    0
    10
    20
    32
    44
    53
    60
    70
    77
    87
    11
    Student 008
    188
    Staff 20
    0
    10
    20
    32
    44
    52
    59
    69
    76
    86
    12
    Student 009
    133
    Staff 15
    0
    10
    20
    32
    44
    52
    59
    69
    76
    86
    13
    Student 010
    124
    Staff 13
    0
    10
    20
    32
    44
    52
    59
    69
    76
    86
    14
    Student 011
    174
    Staff 19
    0
    10
    20
    32
    44
    52
    59
    69
    76
    86
    15
    Student 012
    142
    Staff 16
    0
    10
    20
    32
    44
    52
    59
    69
    76
    86
    16
    Student 013
    86
    Staff 10
    0
    10
    20
    32
    44
    52
    59
    69
    76
    86
    17
    Student 014
    156
    Staff 17
    0
    10
    20
    32
    44
    52
    59
    69
    76
    86
    18
    Student 015
    18
    Staff 02
    0
    10
    20
    32
    44
    52
    59
    69
    76
    86
    19
    Student 016
    3
    Staff 01
    0
    10
    19
    31
    43
    51
    58
    68
    75
    85
    20
    Student 017
    169
    Staff 19
    0
    9
    18
    30
    42
    50
    57
    67
    74
    84
    21
    Student 018
    126
    Staff 14
    0
    9
    18
    30
    42
    50
    57
    67
    74
    84
    22
    Student 019
    159
    Staff 18
    0
    9
    18
    30
    42
    50
    57
    67
    74
    84
    23
    Student 020
    53
    Staff 06
    0
    9
    18
    30
    42
    50
    57
    67
    74
    84


    See the last tab of the workbook at https://app.box.com/s/5a06b6he6sc9ce89bbfl
    Entia non sunt multiplicanda sine necessitate

  3. #3
    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,900

    Re: RANDBETWEEN Function with conditions

    Another way...

    Please Login or Register  to view this content.
    How to install your new code
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
    [B][I]


    In B2, use the following array formula, copied down to B201:
    =IFERROR(INDEX($G$2:$G$21,MATCH(0,--(COUNTIF($B$1:B1,$G$2:$G$21)=$H$2:$H$21),0)),"")

    This generates 10 entries for Staff 1, !o for Staff2, etc...

    The red cells are where the macro will run.

    Click the button. RAND() inserts random numbers and then converts them to values (stopping them changing every time you do something.

    NOW sort columns B&C, based on column C. Make sure that you EXCLUDE column A.

    Done.

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    I forgot to add, the red shading is INTENTIONAL. It tells the macro which cells to generate the random numbers in....
    Attached Files Attached Files
    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

  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,900

    Re: RANDBETWEEN Function with conditions

    Duhh. I forgot to copy/paste the values in column B....

    Please Login or Register  to view this content.

    Amended sheet attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-25-2020
    Location
    France
    MS-Off Ver
    2007
    Posts
    3

    Re: RANDBETWEEN Function with conditions

    Hi,

    I am sorry if this in not the correct place for such help request, if this is the case please let me know.
    I have the following issue, any help would be much appreciated as I am not an excel expert at all...

    I have a list of cases B4:B1003 with corresponding owners C4:C1003, few rows are empty (different numbers of cases each month).
    For quality check, I want to assign 10% of total number of cases (number of cases to be reviewed calculated in I2) to 5 team members (table E4:I23).
    The team members must not review their own cases (column C), they should not review a case already assigned to another team member and there must not be a duplicate in the total list of cases to be reviewed.

    I finally did :
    for the first team mamber =INDEX($B$4:$B$1003;RANDBETWEEN(1;COUNTIFS($C$4:$C$1003;"<>User 1";$C$4:$C$1003;"<>")))
    the second one =INDEX($B$4:$B$1003;RANDBETWEEN(1;COUNTIFS($C$4:$C$1003;"<>User 2";$C$4:$C$1003;"<>";$C$4:$C$1003;"<>$E$4:$E$23")))
    the third one =INDEX($B$4:$B$1003;RANDBETWEEN(1;COUNTIFS($C$4:$C$1003;"<>User 3";$C$4:$C$1003;"<>";$C$4:$C$1003;"<>$E$4:$F$23")))
    etc... adding a column each time to avoid preassigned cases to other team members in the last condition criteria of the formula

    I tryed with index, randbetween, coutifs, rank but I am not good in VBA... I am a bit lost and need your help because using a large amount of case I get some mistakes (duplicates or case assigned to the owner for review).

    Many thanks in advance for your support!
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: RANDBETWEEN Function with conditions

    LovelyDay83,
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ben Van Johnson

+ 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. Replies: 10
    Last Post: 07-03-2015, 04:29 PM
  2. If and Randbetween Function
    By dr_phd1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-19-2013, 04:02 PM
  3. RANDBETWEEN function capped
    By derek594 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-30-2013, 11:14 PM
  4. Randbetween Function
    By CV_GAS in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2008, 03:45 PM
  5. Freezing the RANDBETWEEN function
    By zuri125 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-11-2008, 01:58 AM
  6. randbetween function
    By Tracey in forum Excel General
    Replies: 5
    Last Post: 02-10-2006, 08:35 PM
  7. why doesnt my RANDBETWEEN function work?
    By Edd C in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11: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