1

In a LibreOffice Calc I am trying to retrieve the value from a random cell within a group of cells, some of those cells may contain the word "None", I don't want any of those chosen when the random cell is chosen...example:

Input Output
Maybe Time
Time Time
Wants Maybe
None Wants
Of Tell
My My
Effort Time
None Can
Can My
Tell Effort

However, the only randomization methods I've been able to figure out for myself do full randomization, example:

Input Output
Maybe None
Time Time
Wants Maybe
None Wants
Of None
My My
Effort Time
None Can
Can None
Tell Effort
3
  • I rolled back your edit since we need to be able to copy the examples to actually work with them, and we cannot copy images.
    – terdon
    Commented May 12, 2023 at 12:56
  • that's fine, I just couldn't make the tale appear correctly, that's why I even did the images as it would be easier to understand than a faulty table.
    – DeJeL
    Commented May 12, 2023 at 12:59
  • Yeah, not your fault: the preview looked fine, but it didn't work on posting. It's a bug that has been reported but hasn't been fixed yet: Markdown Table Display Bug
    – terdon
    Commented May 12, 2023 at 13:18

2 Answers 2

1

The best way I see to do this is by filtering out "None" from the list by going to Data > More > Standard Filter.

Otherwise, you're looking at either array formulas or macros. For a discussion of possible solutions, see https://ask.libreoffice.org/t/function-formula-to-filter/55461.

In case you can use Google Sheets instead, there is a FILTER() function that can do on-the-fly filtering.

3
  • the only remaining issue I have after that is that function only sorts the "None"s out... I still need to randomize the remaining non-(visually)blank cells... counta counts all cells that contain anything even formula's outputting ""... do you know how I could do that?
    – DeJeL
    Commented May 13, 2023 at 6:48
  • I figured out a way... I used =INDIRECT("B"&RANDBETWEEN(2, SUMPRODUCT($A$2:$A$11<>""))) (Input was A, sorted using formula was B & randomized output was C) If you edit your response incorporating the two formulas into it I'll mark yours as the answer.;,;.
    – DeJeL
    Commented May 13, 2023 at 7:07
  • @DeJeL: Glad you were able to get it to work. I'll leave it up to you whether to add another answer that explains a specific step-by-step arrangement and formulas, as there are perhaps different ways that could be done. Or simply accept this one, as it seems to have helped, and you've already given a formula solution in the comment. Answered questions have the benefit of being closed out properly, making it easier for others to find the information.
    – Jim K
    Commented May 15, 2023 at 16:21
-2

If you need to retrieve a cell from list of cells, you use INDEX(). If you want to get a random number, you use RANDBETWEEN(). And now, just combine them:

Assuming you have your "input" list in the A column, you can do a formula:

=INDEX($A$2:$A$11, RANDBETWEEN(1, COUNTA($A$2:$A$11)))

Or, if you assign a name to the input range, the formula can be more readable:

=INDEX(src, RANDBETWEEN(1, COUNTA(src)))

Now hit F9 many times and enjoy a random word from the list.

1
  • 2
    This doesn't answer the question, which is how to ignore results where input is "None".
    – Jim K
    Commented May 12, 2023 at 15:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .