Random phrases?

Discuss the spreadsheet application
Post Reply
Caters
Posts: 16
Joined: Tue Jul 26, 2016 8:46 am

Random phrases?

Post by Caters »

I want to be able to use something similar to RAND for text. For example in the illness section I might want it to say "Ill", "Very ill", or "Not ill" at random. The only way I know to do that is to translate text to numbers and use the RANDBETWEEN function(in this case it would be -2 to 0, -2 for very ill and 0 for not ill since illness is a negative factor in lots of things).

Sure, if I am making a formula like I am here for attraction, I might want to do that text to number translation. But what if I am not making a formula but still want text to be random?

Is there a way to do it with just text and not having to do a text to number translation?
OpenOffice 4.1.2 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Random phrases?

Post by Villeroy »

=INDEX({"very ill";"ill";"not ill"};RANDBETWEEN(1;3))
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
Hairyloon
Posts: 5
Joined: Mon Jan 16, 2017 11:43 am

Re: Random phrases?

Post by Hairyloon »

Sorry if this is a breach of etiquette, but it makes sense to me as it is a development of OP.

How can I pick a random phrase off a chart and is it possible to weight it? (eg 50% chance of blue, 20% red 30% green).
OOO330m20 On Windows 7.
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Random phrases?

Post by karolus »

Hallo

Code: Select all

=INDEX({"blue";"blue";"blue";"blue";"blue";"red";"red";"green";"green";"green"};RANDBETWEEN(1;10))
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Hairyloon
Posts: 5
Joined: Mon Jan 16, 2017 11:43 am

Re: Random phrases?

Post by Hairyloon »

karolus wrote:Hallo

Code: Select all

=INDEX({"blue";"blue";"blue";"blue";"blue";"red";"red";"green";"green";"green"};RANDBETWEEN(1;10)) 
Thanks, but that's not picking off a table, and surely not the best way to do it except for just a small number of choices.
OOO330m20 On Windows 7.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Random phrases?

Post by Villeroy »

INDEX($X$1:$X$1000;RANDBETWEEN(1;1000)
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
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: Random phrases?

Post by gerard24 »

INDEX/MATCH RAND in cumulative weight. Look at attached file :
weighted_random.ods
(9.93 KiB) Downloaded 112 times
LibreOffice 6.4.5 on Windows 10
Hairyloon
Posts: 5
Joined: Mon Jan 16, 2017 11:43 am

Re: Random phrases?

Post by Hairyloon »

gerard24 wrote:INDEX/MATCH RAND in cumulative weight. Look at attached file :
weighted_random.ods
Thanks (I think). I can't quite work out how it works, but I'll look a bit harder before I admit to that. ;)
OOO330m20 On Windows 7.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Random phrases?

Post by Villeroy »

Remove the percent formatting from C2:C4 and you'll understand better.
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
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: Random phrases?

Post by gerard24 »

RAND() returns a decimal number in the interval [0,1[ (eq [0%,100%[) with equal probability.

MATCH without 3rd argument (or equals to 1) returns the position of RAND result in C2:C4
F1 Help wrote:If matchtype is 1 or omitted, searchregion is assumed to be sorted in ascending order. MATCH then returns the position of the largest value in searchregion that is less than or equal to searchitem.
Every RAND result from 0 to 0.5 (smallest than 0.5) returns 1, from 0.5 to less than 0.7 returns 2, higher than 0.7 returns 3.

So you have 50% chances to have 1, 20% to get 2... etc

INDEX returns the corresponding color.
LibreOffice 6.4.5 on Windows 10
User avatar
soby
Volunteer
Posts: 150
Joined: Sat Oct 29, 2016 10:49 am

Re: Random phrases?

Post by soby »

i don't now if i have well understand the topic
i have read only the above question of Caters
Attachments
randbetween illness.ods
(10.95 KiB) Downloaded 113 times
Libre Office 6.1 dev homebuild Open Office 4.1.5 on Slackware64 current
Hairyloon
Posts: 5
Joined: Mon Jan 16, 2017 11:43 am

Re: Random phrases?

Post by Hairyloon »

Thank you.
Probably a silly question now, but how do I get it to go again? ie give another random result. :oops:
OOO330m20 On Windows 7.
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: Random phrases?

Post by gerard24 »

Hairyloon wrote:Thank you.
Probably a silly question now, but how do I get it to go again? ie give another random result. :oops:
Press the F9 key.
LibreOffice 6.4.5 on Windows 10
User avatar
soby
Volunteer
Posts: 150
Joined: Sat Oct 29, 2016 10:49 am

Re: Random phrases?

Post by soby »

i think it is Ctrl+Shift+F9 for AOO anf F9 for LO
Last edited by soby on Wed Jan 18, 2017 2:59 pm, edited 1 time in total.
Libre Office 6.1 dev homebuild Open Office 4.1.5 on Slackware64 current
Hairyloon
Posts: 5
Joined: Mon Jan 16, 2017 11:43 am

Re: Random phrases?

Post by Hairyloon »

soby wrote:i think it is Ctrl+Shift+F9
Seems to work, thank you.
OOO330m20 On Windows 7.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Random phrases?

Post by Lupp »

Ctrl+Shift+F9 will force a complete recalculation of all the formulae in all the sheets of the Calc document.

A formula containing a 'volatile' function, here to name NOW() and RAND(), will recalculate on any event if 'AutoCalculate' is on.
RANDBETWEEN is not of the volatile group.

Suppose you want a way to order recalculation only for a specific group of formulae, you can modify the formulae by a trigger.
Taking the formula posted above by Villeroy (completed version) as an example:

Code: Select all

=INDEX($X$1:$X$1000;RANDBETWEEN(1;1000))
you can use

Code: Select all

=INDEX($X$1:$X$1000;RANDBETWEEN(1;1000)+N(A1)*0)
e.g. The part +N()*0 will always just add 0 and thus do nothing. On the other hand it will be reacalculated whenever something happens to cell A1. Thus you can use A1 to trigger recalculation for all the formulae containing that part.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply