How can I get a random cell in OpenOffice Calc?

Question

I have an array of values Sheet1.B3:B9. On another sheet, I'm trying to get a random value from one of those cells.

I suppose it has to be something with the RANK() function, but I'm grasping at straws.

How can I get the value of a random cell in that range?

Answer 1

In LibreOffice Calc to get a random cell between cells B3 and B9 of Sheet1 you can do:

=INDEX(Sheet1.B3:B9, RANDBETWEEN(1,5))

Answer 2

In Excel, this will give random lookup into the range

=INDEX(Sheet1!$B$3:$B$9,INT(RAND()*7)+1)

Or you can give your source data range a name

=INDEX(RandDataSource,INT(RAND()*ROWS(RandDataSource))+1)

Don't know if it applies to Openoffice