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

by

first, 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!
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 8 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()})))

https://i.imgur.com/qIpobyO.png

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/8 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()}))))

https://i.imgur.com/MqkvTEN.png

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

https://i.imgur.com/TEARjgg.png

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"))

https://i.imgur.com/xZaf7F1.png

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, ))

https://i.imgur.com/e2ryNfb.png

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))

https://i.imgur.com/Ug2UGEJ.png

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)))

https://i.imgur.com/fssIdtC.png

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)), " ", ))

https://i.imgur.com/mI7jt0M.png

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)

https://i.imgur.com/Gn4z6J5.png

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:

enter image description here

More Related Contents:

  1. ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets
  2. Conditional formatting based on another cell’s value
  3. Google Sheets importXML Returns Empty Value
  4. Get the last non-empty cell in a column in Google Sheets
  5. How to SUM duration in Google Sheets?
  6. ArrayFormula and “AND” Formula in Google Sheets
  7. ARRAY_LITERAL, an Array Literal was missing values for one or more rows
  8. Query is ignoring string (non numeric) value
  9. Repeat whole row N times based on column value in Google Sheets
  10. I get Formula parse error with all formulas
  11. IMPORTXML and right XPath from Bloomberg price [duplicate]
  12. What factors determine the memory used in lambda functions?
  13. How to list all permutations without repetition?
  14. How to use arrayformula with formulas that do not seem to support arrayformulas?
  15. Repeat each row N times in Google Sheets
  16. How to create all possible pair combinations without duplicates in Google Sheets?
  17. Col BY causing parse error in Google sheets query
  18. Count cells that contain any text
  19. How to make a range repeat n-times in Google SpreadSheet
  20. In ARRAY_LITERAL, an Array Literal was missing values for one or more rows – indirect formula error in google spreadsheet
  21. How to create a runing total formula that expands automatically when new rows are added to my spreadsheet [duplicate]
  22. No permission to call msgBox in Google Apps Scripting
  23. Periodically refresh IMPORTXML() spreadsheet function
  24. Selecting the last value of a column
  25. Download link for Google Spreadsheets CSV export – with Multiple Sheets
  26. Stacking multiple columns on to one?
  27. Google Spreadheets Scripts: check if cell is empty
  28. How to Print sheet/range using .gs script in Google Sheets?
  29. Google Sheets QUERY Function: Select Columns by Name
  30. How to maintain absolute cell address even when deleting ranges?

Leave a Comment