Conditional Random Draws of 3 values

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.

[Juan Pablo González]

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.

[Chris Davison]

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

[Juan Pablo González]

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.

[Chris Davison]

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

!