Ranking with random weights

[FivestarMac]

I have asked Ben to rank his favorites from a list of 10 things. He can give each a 1, rank them as I have in this example, give all points to one choice...any combination, BUT, he must always use up the 10 points I have given. Question, how can I create equations which will do this in a random fashion for other people in columns c, D, E, etc.?

[Crooza]

Try this in C2

=RANDBETWEEN(0,10)

then this in c3 and drag down

=RANDBETWEEN(0,10-SUM(C$2:C2))

but it does tend to bias the top cells.

[FivestarMac]

G'day, Crooza, and thanks for this quick solution. It works for my purposes even though it tends to bias the top cells.

[FivestarMac]

Crooza, what is the function of the dash (-) between 10 and SUM in the second equation?

[FivestarMac]

OOPS! Just figured out that it is a minus sign.

[Crooza]

Yep a minus sign. So that the total never exceeds 10

[FivestarMac]

Right. Anyway, I am always in awe of you guys. I try to solve my problems using solutions in close parallel from the Forum, but when I have failed I put the question to the Forum and usually, while I am still asleep, somebody from around the world puts forward an answer. Y'all are a very select cadre of bright fellows (and gals, I suppose!)

[thuythanh]

Change value cell E1

[FivestarMac]

Thanks, thuythanh, that is a good solution which does not favor the top cells