How can I adjust this spreadsheet formula to take a random *non-blank* value from the specified range?

user_137629 0
Viewed 29

I am trying to achieve a certain unusual result using Google Sheets and here's what I've got so far.

My formula takes the value of a randomly selected cell within the named range "IntroLines":

=INDEX(IntroLines,RANDBETWEEN(1,ROWS(IntroLines)),1)

The only problem with it is that some of the cells in that named range are blank and I would like NOT to select those cells. Any ideas how I could update this formula to enforce that a non-blank value is selected?

Note: I would strongly prefer to solve this problem without removing blank cells from the named range.

spreadsheet formula google-sheets if-statement random
1 Answers

Try:

=INDEX(SORT(IF(IntroLines="",,{IntroLines, RANDARRAY(ROWS(IntroLines))}), 2, 1), 1, 1)
user_56326290
Related