0

I’m trying to emulate Minesweeper in Google Sheets, and for this I want to create a second map adjacent to the first with all of the correct values already in it. To randomize bomb position, I need a list of random numbers or cells(cells would be preferable). However, I cannot figure out how to do this without ending up repeating numbers. The result would ideally be a vertical array of cell coordinates. Thank you!

Fish In a Tree 0
  • 15
  • 4

1 Answers1

1

Answer

The following formula should produce the result you desire:

=SORTN(FLATTEN(MAKEARRAY(10,10,LAMBDA(row,col,ADDRESS(row,col)))),20,,RANDARRAY(100),)

In =MAKEARRAY, change the first 10 to adjust how many rows to randomly choose from, or the second 10 to adjust how many columns to choose from. The value in =RANDARRAY must be equal to the product of the number of rows and the number of columns. (e.g. in the above example, 10*10=100).

Change the 20 to adjust how many randomly chosen values to return.

Explanation

=MAKEARRAY is used to generate an array of every possible row and column combination. It accepts a =LAMBDA, which in this case is just the =ADDRESS function. The first two arguments of =MAKEARRAY determine how large the array should be, which is why changing them adjusts how many rows/columns to randomly pick from.

Then, the result of =MAKEARRAY is squashed into a single column using the =FLATTEN formula.

Finally, the entire thing is sorted randomly using =SORTN combined with =RANDARRAY. =SORTN also limits the number of results that are returned dependent on its second argument, which is why changing it adjusts how many results are returned.

If you want information on how to "freeze" the value of =RANDARRAY so it doesn't recalculate each time you change something, check out this question by player0.

Functions used:

Sebastian Smiley
  • 831
  • 4
  • 15