Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 01 Jul 2016 19:28:03 +0200How can I select a random list of names from a group in Calc?https://ask.libreoffice.org/en/question/9814/how-can-i-select-a-random-list-of-names-from-a-group-in-calc/is there a formula that will select a random list of names from a large group using Calc?
No repeats in selection required.
Thanks
Sat, 12 Jan 2013 05:51:59 +0100https://ask.libreoffice.org/en/question/9814/how-can-i-select-a-random-list-of-names-from-a-group-in-calc/Answer by JohnSUN for <p>is there a formula that will select a random list of names from a large group using Calc?
No repeats in selection required.</p>
<p>Thanks</p>
https://ask.libreoffice.org/en/question/9814/how-can-i-select-a-random-list-of-names-from-a-group-in-calc/?answer=9857#post-id-9857Another way to solve this problem is described in this file - [Random names.ods](/upfiles/13581575538937938.ods)Mon, 14 Jan 2013 10:59:37 +0100https://ask.libreoffice.org/en/question/9814/how-can-i-select-a-random-list-of-names-from-a-group-in-calc/?answer=9857#post-id-9857Comment by gingerling for <p>Another way to solve this problem is described in this file - <a href="/upfiles/13581575538937938.ods">Random names.ods</a></p>
https://ask.libreoffice.org/en/question/9814/how-can-i-select-a-random-list-of-names-from-a-group-in-calc/?comment=72647#post-id-72647Thanks, these docs were great, I re-did them with up-dated screenshots. Hope that helps anyone who needs them http://www.gingerling.co.uk/how-to-randomly-select-data-from-a-column-in-libreoffice/Fri, 01 Jul 2016 19:28:03 +0200https://ask.libreoffice.org/en/question/9814/how-can-i-select-a-random-list-of-names-from-a-group-in-calc/?comment=72647#post-id-72647Answer by qubit for <p>is there a formula that will select a random list of names from a large group using Calc?
No repeats in selection required.</p>
<p>Thanks</p>
https://ask.libreoffice.org/en/question/9814/how-can-i-select-a-random-list-of-names-from-a-group-in-calc/?answer=9834#post-id-9834Hi @cflyte,
Could you be a little more specific about the formula you're drafting?
- Do you want a formula that will select a variable # of random names from a long list, or a formula that will select a specific # of random names from a long list?
- You say `"No repeats in selection required."` -- Are you implying that repeats in the selection are acceptable? (this nuance is important when designing the formula!)
Here's a quick formula for grabbing random names out of a list -- see if this works for you:
In a new Calc spreadsheet, I added the following names:
---+----------+-------------
| A | B
---+----------+-------------
1 | Jim |
2 | Jon |
3 | Jem |
4 | Jen |
5 | Jan |
6 | Jam |
7 | Archibald|
In a separate column (I just chose cell B2), I used the following formula to select a random cell from my range
=INDIRECT("A"&RANDBETWEEN(1,COUNTA(A1:A7)))
I can get a list of random names by copying this formula into several cells B2 through B10 (etc...). Note that this list may have repeats.
You'll want to tweak the arguments to INDIRECT() and RANDBETWEEN() so that they accurately describe the bounds of your range. Otherwise you may get spurious results in your output stream (e.g. selecting a larger range than your data resulting in selection of empty cells, or selecting a smaller range and never picking certain names)Sun, 13 Jan 2013 15:47:12 +0100https://ask.libreoffice.org/en/question/9814/how-can-i-select-a-random-list-of-names-from-a-group-in-calc/?answer=9834#post-id-9834