google-sheets random google-sheets-formula spreadsheet volatile

How to stop / freeze / pause volatile RAND / RANDBETWEEN / RANDARRAY?

is there an easy way (workaround) how to disable automatic re-calculations of volatile functions like =RAND() =RANDARRAY() =RANDBETWEEN() in google spreadsheet (?)

without scripts
without addons
without macros

in case of building a key generator, where we need to work with multiple RANDBETWEEN outputs, a re-calculation takes a place on every cell change, and those RANDBETWEEN numbers can't stay for a longer period of time in our sheet, which we constantly edit.

there's a buggy freezer addon out there and lots of non-universal script variations requiring installation & some degree of modding / knowledge, so how to do this the old way?

Solution

UPDATE: since 20/09/2022 we are able to freeze all volatiles:

NOW
TODAY
RAND
RANDBETWEEN
RANDARRAY
COINFLIP
WHATTHEFOXSAY

at a whim with a draggable LAMBDA function (until someone figures out how to apply LAMBDA-volatile to an array without helper columns). here are few examples:

timestamp upon cell edit:

=LAMBDA(x; x)(IFERROR(A1/0)+NOW())

timestamp based on checkbox:

=LAMBDA(x; x)(IF(A1; NOW(); ))

timestamp upon range edit:

=LAMBDA(x; x)(IFERROR(A1:A10/0)+NOW())

non-dependant custom timestamp:

=LAMBDA(x; x)(TEXT(NOW(); "dddd dd mmmm e hh:mm:ss.000")

random number:

=LAMBDA(x; x)(RAND())

array with random numbers:

=LAMBDA(x; x)(RANDARRAY(4; 2))

random boolean (schrodinger cat style):

=LAMBDA(x; x)(COINFLIP())

random number between 1 - 10:

=LAMBDA(x; x)(RANDBETWEEN(1; 10))

grid 5×3 of random numbers between 1 - 10:

=INDEX(LAMBDA(x; x)(RANDBETWEEN(SEQUENCE(5; 3; 1; 0); 10)))

generate hex code 9 characters long:

=LAMBDA(x; x)(DEC2HEX(RANDBETWEEN(0; HEX2DEC("FFFFFFFFF")); 9))

generate hex code 16 characters long:

=INDEX(JOIN(; LAMBDA(x; x)(DEC2HEX(RANDBETWEEN({0; 0}; 16^8); 8))))

random column later from A - Z:

=LAMBDA(x; x)(CHAR(RANDBETWEEN(65; 90)))

select a random cell from a range A2:A10:

=INDIRECT("A"&LAMBDA(x; x)(RANDBETWEEN(2; 10)))

freezing the fox to fix refresh requirement

=LAMBDA(x; x)(WHATTHEFOXSAY())

random alphanumeric of length 8 characters:

=LAMBDA(x; x)(JOIN(; BYROW(SEQUENCE(8); LAMBDA(x; IF(COINFLIP(); IF(COINFLIP(); 
 CHAR(RANDBETWEEN(65; 90)); CHAR(RANDBETWEEN(97; 122))); RANDBETWEEN(0; 9))))))

random alphanumeric of length 8 characters with different distribution:

=LAMBDA(x; x)(JOIN(; BYROW(SEQUENCE(8); LAMBDA(x; SINGLE(SORT(CHAR({SEQUENCE(10; 1; 48); 
 SEQUENCE(26; 1; 65); SEQUENCE(26; 1; 97)}); RANDARRAY(62; 1); ))))))

let's see what says the fox

=WHATTHEFOXSAY()

is a unique easter egg google sheets function (discovered by @kishkin) that randomly generates a pre-set string of text on user demand which is a huge deal because while the generation is random, the recalculation is not affected by onEdit, onChange nor onOpen events, so with some tweaking, we can generate a random number without using volatile functions like RAND(), RANDBETWEEN(), ARRAYRAND() which can't be freezed / paused

the fox can speak only these phrases:

A-oo-oo-oo-ooo!
A-hee-ahee ha-hee!
Hatee-hatee-hatee-ho!   
Wa-pa-pa-pa-pa-pa-pow!  
A-bubu-duh-bubu-dwee-dum    
Fraka-kaka-kaka-kaka-kow!   
Jacha-chacha-chacha-chow!   
Gering-ding-ding-ding-dingeringeding    
Joff-tchoff-tchoff-tchoffo-tchoffo-tchoff!

eg. what we are getting with this function is random 1 of 9 chance, which is not much, so we will need to scale it up (let's say by order of 9 fox tails). sadly function is not supported by ARRAYFORMULA, therefore we need to construct {} an array:

={WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}

then we convert this fox language into numbers like:

=INDEX(LEN(
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}))

next, we need a switch. nothing fancy, just a simple IF statement and one checkbox:

=ARRAYFORMULA(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))

this will give us the ability to have full control over recalculation simply by switching the checkbox. and the last step is to scale up our initial 1/9 chance by adding more variations into our randomness by multiplication of all numbers with PRODUCT and for more fun we multiply it by PI raised on 3rd power:

=ARRAYFORMULA(PI()^3*PRODUCT(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}))))

unlimited blade works

so far it doesn't look like we created something useful... a checkbox with some randomly looking number cycling back and forth (which is at this point not random at all tho)... so let's move to the next level. our true objective is to create a string of digits from which we extract certain parts and convert them to final output. for that we need to harness the power of SEQUENCE which we TRANSPOSE for extra power:

=TRANSPOSE(SEQUENCE(5, 4, 3, 2))

this translates as a grid of 5 columns times 4 rows where the first cell starts with number 3 and the value of every next cell (column-wise) is larger by 2

now let's define our final output:

3x unique strings
each string has 16 characters
with numbers from 0 to 9
and lower case letters from a to f

at this point our SEQUENCE will look like this:

=TRANSPOSE(SEQUENCE(16, 3, 29, 73))

16 = columns
3  = rows
29 = starting point
73 = stepping

for the 3rd and 4th argument (29 & 73) we can use some nice prime numbers and multiply our whole sequence again by PI()^3 for extra chaos. also we need to convert our numeric output into a plain text string to avoid 1.79769E+308 notation so we use TEXT for that:

=ARRAYFORMULA(TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
 TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"))

at this point, we have a pretty nice distribution of digits across the whole grid

ultimate hex / password generator

every character out there has its own code. you can check it by running this AF in your sheet:

=ARRAYFORMULA({{"character", "code"};
 {CHAR(SEQUENCE(2500, 1, 33)), SEQUENCE(2500, 1, 33)}})

ROW(48:57)     = 0-9
ROW(65:90)     = A-Z
ROW(97:122)    = a-z
ROW(1040:1071) = А-Я
ROW(1072:1103) = а-я
ROW(913-937)   = Α-Ω
ROW(945-969)   = α-ω

for our example purpose we need character codes for 0-9 and a-f:

ROW(48:57)     = 0-9
ROW(97:102)    = a-f

we put it in array {} and sort it in descending order (it's because we don't want to lock ourselves out from reaching lower positioned characters on the list). then we JOIN it with or | separator ready to be regex-ed:

=JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))

basically, the idea is to REGEXEXTRACT first found number from the above-joined string within our grid of numbers, and just in case there is no match we add IFNA fallback with some number (102 in this example) from our ranges:

=ARRAYFORMULA(IFNA(REGEXEXTRACT(
 TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
 TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"), 
 JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))), 102))

and summon the CHAR characters:

=ARRAYFORMULA(CHAR(IFNA(REGEXEXTRACT(
 TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
 TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"), 
 JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))), 102)))

now the last step is to smash it with a query and remove residue empty spaces - more on query smash here

=ARRAYFORMULA(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(
 CHAR(IFNA(REGEXEXTRACT(TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
 TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"), 
 JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))), 102))),,
 9^9)), " ", ))

summary

are those strings really random? ofc not - they are just "random enough" because the "entropy" rules are known to us, but good luck cracking it by hand...
the main key point is that there is no 3rd party recalculation happening so the values stay paused and fresh values are generated upon switching the checkbox
will these strings repeat over a period of time? - yes they may, just as regular RAND() function
is there a possibility of generating two identical keys on one go? - yes, it may happen, but it can be countered by several ways to lower the chances or introduce UNIQUE() or adding more fox tails
please note this special occasion: onOpen event will re-calculate WHATTHEFOXSAY() one time after every checkbox switch !! so it's recommended to refresh the spreadsheet with F5 key after every checkbox switch you make if you need to work with frozen value

in case you wish to have a fresh automatically generated string in certain periods of time you can insert a volatile function TODAY() or NOW() - but note that recalculation will happen every time you edit any cell or every minute / hour when you don't edit anything
(depending on ..//File > Spreadsheet settings)

how about not to re-calculate on every cell change and re-calculate on every checkbox switch or every 6 minutes? once a week? every odd month? only mondays and wednesdays? every 11th hour? possible! we need one more simple IF statement placed anywhere in the spreadsheet which will host our logic gate and output the values in array {} constellation so we could refer with our fox formula to a cell which does not hold formula with volatile elements. example:

randbetween until a specific day? why not:

=IF(F2<F1, RANDBETWEEN(1, 10), 
 UNIQUE(REGEXEXTRACT(QUERY(SPLIT(FLATTEN(LEN(
 WHATTHEFOXSAY()&WHATTHEFOXSAY())&" "&TRANSPOSE(LEN(
 WHATTHEFOXSAY()&WHATTHEFOXSAY()))), " "), 
 "select Col1+Col2 label Col1+Col2''")&"", "\d$")+1))