[SOLVED]Random picks from Row

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

I have a row (A1-T1) consisting of 20 numbers, and would like to randomly pick 5 of those into another row (A2-E2), how would I do that?

thanks for any answer and have a great day!

Villeroy · Posted: Sun Jun 17, 2012 8:31 am Post subject:

Get another row of 20 cells, fill them with =RAND(), say AA1:AT1
some cell: =INDEX($A$1:$T$1 ; 1; RANK(AA$1 ; $AA$1:$AT$1)) [EDIT: fixed version]
copy to the right, hit F9 to refresh

RAND generates 10 different values in $AA$1:$AT$1
RANK(AA$1;$AA$1:$AT$1) gives the rank of AA1 within the array of randoms.
When copied to the right we get 20 ranks between 1 and 20 in random order because the input values are random.
The index function gets a value from the original data at the position of the random ranking.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

fivefootnine · Power User Joined: 09 Apr 2012 Posts: 60

Thank you for your answer, it doesnt quite work though. I´m doing exactly as you wrote but I´m getting the #VALUE! in the cells where I put the code.??

Villeroy · Posted: Sun Jun 17, 2012 10:31 am Post subject:

I made a mistake. Try the fixed formula.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org