+ Reply to Thread
Results 1 to 5 of 5

Divide RANDNUMBER into multiple cells with min/max requirements

  1. #1
    Registered User
    Join Date
    02-05-2017
    Location
    Fort Lauderdale, Fl
    MS-Off Ver
    Open Office
    Posts
    2

    Divide RANDNUMBER into multiple cells with min/max requirements

    Hello! I'm trying to figure out how I would divide a random number generated by the RANDNUMBER function into multiple cells.

    Requirements:
    - Numbers must be whole numbers
    - Random number generated will always be between 30 and 60
    - There are 5 cells the number must be divided into, however each cell has a Minimum of 1, and different Max values.

    EXAMPLE

    A1 - Max Value 30
    A2 - Max Value 30
    A3 - Max Value 10
    A4 - Max Value 10
    A5 - Max Value 20

    The total of the values in A1 to A5 cannot exceed the value randomly generated from 30-60. The minimum in any given cell must be at least 1.

    I'm not sure if there is a way to do this or not, but after doing some of my own research I couldn't seem to find anything. I'm also an extreme novice with excel functions, however, so I'm hoping someone more knowledgeable may have a solution! Thank you

  2. #2
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: Divide RANDNUMBER into multiple cells with min/max requirements

    Hi
    do you have to distribute a given random number to the cells, or could you just use the formula =randbetween(1,30) in cell A1, =randbetween(1,20) in A5 etc and loop until the sum of A1:A5 is between 30 and 60?

  3. #3
    Registered User
    Join Date
    02-05-2017
    Location
    Fort Lauderdale, Fl
    MS-Off Ver
    Open Office
    Posts
    2
    Quote Originally Posted by NickyC View Post
    Hi
    do you have to distribute a given random number to the cells, or could you just use the formula =randbetween(1,30) in cell A1, =randbetween(1,20) in A5 etc and loop until the sum of A1:A5 is between 30 and 60?
    It would have to be the former yes. The problem with the solution you're suggesting is that the total would actually be more like randomizing between 1-100 which creates a noticeable difference in the final total of the five cells often being 60 or close to, and thus not an even random distribution. I already tried it myself as well with those unfortunate results.

  4. #4
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: Divide RANDNUMBER into multiple cells with min/max requirements

    Ah I see the problem

    if your computer speed is not too bad you could try reversing the process - selecting a random number between 30 and 60, having =randbetween(1,x) formulas in cells a1:a5, and recalculating until the sum matches your random number. I did it with a macro and it took less than a second.

    If you have a cell named "target" containing the formula
    = randbetween(30,60)

    and a cell named "fixed" to copy the value generated by that formula into
    and a cell named "cellsum" with the sum of cells A1:A5

    This macro will recalc the sheet until "cellsum" matches your chosen random number between 30 and 60

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Divide RANDNUMBER into multiple cells with min/max requirements

    Try:

    Please Login or Register  to view this content.
    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] Divide a number into multiple cells
    By rhon101 in forum Excel General
    Replies: 6
    Last Post: 03-03-2020, 08:20 PM
  2. Divide a number into multiple cells based on logic
    By Jeff D in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-26-2017, 12:30 PM
  3. [SOLVED] Take Cells from one column and divide up in multiple columns
    By robkrouse in forum Excel General
    Replies: 5
    Last Post: 09-01-2016, 11:00 AM
  4. [SOLVED] Divide a number into multiple cells
    By beitzy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-13-2013, 07:41 AM
  5. [SOLVED] Divide cell by total (multiple cells)
    By Jdijkers in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2013, 07:05 AM
  6. Divide points value between multiple cells
    By CCRich in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-13-2013, 08:50 PM
  7. Removing Divide from multiple cells?
    By 2000m2 in forum Excel General
    Replies: 3
    Last Post: 04-21-2010, 02:15 PM

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