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.

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.