Forum Discussion
RANDARRAY Function
Unless I'm mistaken--a distinct possibility--since you're limiting yourself (and us) to 10 whole numbers, between 1 and 10, in 10 rows and 10 columns, haven't you basically laid out the possibilities in your "simple example"?
You could vary the sequence of rows or columns randomly, but not both at the same time.
Here's one approach, with which I'm not fully satisfied. This uses the dynamic array function SORT, which does require the most recent version of Excel. Maybe it'll point you in a useful direction
mathetes :Thanks for the quick response. I have used the sort function but as you have stated it does not allow me to do both rows and columns at the same time.
This is the formula that I used for the sort function:=SORTBY(SEQUENCE(10,,0,1),RANDARRAY((10)))
This worked great for the randomness but I still have numbers that are the same in rows and columns.
Just for the record, mine used SORT, not SORTBY
Again, I wonder if you haven't set yourself up for actually a fairly limited number of possible combinations precisely because the numbers must be whole integers between 1 and 10, in ten rows and ten columns.
Anyway, another Dynamic Array function to test out is UNIQUE, which can make sure that any one number appears only once. I'd be more confident of meaningful randomness if you allowed integers between 1 and 99, say.
Here's a video on the Dynamic Array functions, in case they're new to you.
https://www.youtube.com/watch?v=9I9DtFOVPIg
