Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Conditional Random Draws of 3 values

  1. #1
    Guest

    Default

    I know the subject sounds like an oxymoron, but here's what I need to do ... I want to be able to draw random numbers between zero and 1 for cells in three columns (e.g., A1, B1 and C1), so that the sum of the 3 random values in these three columns always totals to 1. Is there an Excel function (or groups of functions) that could handle this? I have an idea how to do it with VBA, but it may be quicker with Excel function(s).

    Thanks.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, I feel that this would be better in VBA, but you could have in A1 and in B1 this:

    =RAND()

    And in C1 this

    =1-A1-B1

    Not fully tested, and I'm sure there could be some trouble, but it should work, or at least give you some ideas.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Juan Pablo, I think the rand() in a1 and b1 could return values >0.5 in each cell, so the balance in c1 would be negative

    would this be better ? :

    a1 = rand()
    b1 = RAND()*(1-A1)
    c1 = 1-a1-b1

    also : to infinity, the 3 values would average to 0.33333, 0.33333 and 0.33333 respectively if truly random and I suspect my method doesn't give c1 the chance to boost it's average as there's always 2 cells before it given a chance to be higher than an average 0.333333 :

    I'd agree : VBA can truly randomise it by giving each cell first go in turn



    :: Pharma Z - Family drugstore ::

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is one of the problems I thought of... and limiting each cell to <= 1/3 wouldn't sound very "random" to me....

    Anyway, your Excel solution is better.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks !

    I think VBA would have to randomise which cell starts first (a1 or b1 or c1) and which is 2nd....

    if it was always a1, then b1 and c1 would never be random, they'd always be dependant to a degree on the result of a1, rather than the full "1.0"

    running my solution, say, a thousand times would probably result in averages looking something like :

    0.5, 0.25, 0.25

    !
    :: Pharma Z - Family drugstore ::

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •