Google Sheets Random Numbers – Generating Multiple Random Numbers in a Single Cell

google sheets

I am attempting to create a sheet that reads a cell and generates a set of random numbers based on that cell's length

I have a long list of one to four random numbers, in the format below.

4
2 2
0 4
4 0 0
3 1 0
0 0 4
4 0 0 0
3 0 0 1
2 2 0 0

One of these numbers will be picked at random by another process. I need to generate another random number between 1-4 for each of those numbers. If three numbers are chosen, I need to generate three more numbers. If only one number is chosen, then I need to generate one more number.

I'm not sure how to do this, as I don't appear to be able to use more than one RANDBETWEEN per cell.

Best Answer

If the string you're starting with, say 3 1 0, is in cell A1, the following function generates a random string such as 1 4 1.

=left(join(" ", {randbetween(1,4); randbetween(1,4); randbetween(1,4); randbetween(1,4)}), len(A1))

The idea is to generate an array of four random numbers, join it into a space-separated string, and then cut the part of the string of the same length as the input.

Related Topic