[Solved] Select random data from a list

Discuss the spreadsheet application

[Solved] Select random data from a list

Postby BetaRayBill » Tue May 11, 2021 6:02 am

Hi,
This is the problem: I created a spreadsheet with a simple list of items. I opened another spreadsheet (same document) and wanted to create a cell(s) with a function that randomly selects an item from the previously created list and displays it (renewing each time I reopened the document). The function would take place in many cells, so it would be great if they didn't repeat. I did some research, but it's not as easy to do as I thought - something about assigning each item in the list a random number using Rand(), and then having that selection displayed; but the parameters are confusing and the function eventually doesn't work (Error508, etc.)

Thanks.
Last edited by MrProgrammer on Fri May 21, 2021 4:32 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Open Office 4.1.2
MAC OSX Sierra
BetaRayBill
 
Posts: 7
Joined: Mon Jul 02, 2018 5:34 am

Re: How To Display Data From a List?

Postby Zizi64 » Tue May 11, 2021 12:13 pm

Please upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9943
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How To Display Data From a List?

Postby Villeroy » Tue May 11, 2021 3:52 pm

index_rank.ods
pick 10 random items out of 30
(15.4 KiB) Downloaded 48 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30084
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How To Display Data From a List?

Postby MrProgrammer » Tue May 11, 2021 4:09 pm

BetaRayBill wrote:I opened another spreadsheet (same document) and wanted to create a cell(s) with a function that randomly selects an item from the previously created list and displays it (renewing each time I reopened the document). The function would take place in many cells, so it would be great if they didn't repeat.
Tutorial with examples: Randomization in Calc

BetaRayBill wrote:using Rand() … but the parameters are confusing and the function eventually doesn't work (Error508, etc.)
Read the documentation for the RAND function. It doesn't take any parameters! How can that be confusing?

[Tutorial] Ten concepts that every Calc user should know

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 4077
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Select random data from a list

Postby BetaRayBill » Thu May 13, 2021 9:05 pm

Here is a screenshot of an example I found that I thought matched my problem. It's a list of names, followed by a random set of numbers (using Rand() function). The next column is where the random names are to appear. The function shown looks like it should work (supposedly won't repeat a name), but the output is an error.
Attachments
example.png
Open Office 4.1.2
MAC OSX Sierra
BetaRayBill
 
Posts: 7
Joined: Mon Jul 02, 2018 5:34 am

Re: Select random data from a list

Postby MrProgrammer » Thu May 13, 2021 9:17 pm

BetaRayBill wrote:Here is a screenshot …
Your screen image is not the proper way to get assistance with analyzing the error in your sample problem. I will presume the goal is to present the 15 names in random order. If not, state the goal clearly. Can't you use the example for section E (sheet RandSort) of the tutorial I provided? You will need to attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself) before I can help with your sample problem. A guess though: In OpenOffice you must use semicolon as an operand separator in formulas because using a comma can create Err:508.

Your signature shows you use OpenOffice 4.1.2. You should know that this version has a serious problem which prevents many of the features of Base from working. Even if you don't use Base now, you might want to in the future, for example, to create mailing labels.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 4077
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Select random data from a list

Postby karolus » Fri May 14, 2021 10:22 am

Hallo

@BetaRayBill: Use: =INDEX($A$2:$A$16;RANK(B2;$B$2:$B$16))
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
User avatar
karolus
Volunteer
 
Posts: 889
Joined: Sat Jul 02, 2011 9:47 am

[Solved] Select random data from a list

Postby BetaRayBill » Mon May 31, 2021 8:26 pm

Hey Everyone,
Sorry for these late replies. This particular problem is not an emergency, and I haven't had the time to check back on this forum as much as I would like. Mr. Programmer: my apologies for not being clear. I will look into the semicolon suggestion. Karolus: I plugged in your formula, and that netted the result I wanted - all the names were randomized and didn't repeat. Thank you for that! I will examine the formula to try to figure out exactly how it works so as to understand it better. The actual list I will be extracting from is much larger than the example, so we will see how it works with that. It looks like it should be fine though.

Thanks to all.
Open Office 4.1.2
MAC OSX Sierra
BetaRayBill
 
Posts: 7
Joined: Mon Jul 02, 2018 5:34 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 18 guests