Conditional Random Draws of 3 values

G

Guest

Guest
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0
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

!
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top