Google-sheets – How to generate a Random Number that does not change in Google Sheets

google sheets

I am generating a chart with random data:

Date        Value
1/1/2018    123
1/2/2018    453

I used RAND to generate the values, but don't want them to recalculate all the time. How to suppress the recalculation of RAND?

Best Answer

The short answer is "You can't" (at least not without script). However, unless your work is scientific in nature, you probably don't need "random" numbers, just unpredictable ones. (In fact, to be technical, even RAND doesn't generate true random numbers; rather, it uses a complex algorithm to ... well, generate an unpredictable number.)

The problem, as you know, is that RAND recalculates. But we can write any number of formulas that will produce "pseudo-random" numbers from the date. For instance, assuming that your dates are in Column A and your "random numbers" in Column B, you could put something like this in B2 and copy down as far as needed:

=VALUE(TEXT(INT(((A2*COS(DAY(A2))*PI())-INT(A2*COS(DAY(A2))*PI()))*1000)+IF(INT(((A2*COS(DAY(A2))*PI())-INT(A2*COS(DAY(A2))*PI()))*1000)<100,WEEKDAY(A2)*100,0),"000"))

This will use features of the date to generate a "pseudo-random" 3-digit number.

The overly-complicated formula isn't all that important. I just smashed some math functions together. They could have been endless combinations of others.

However, if you just need a "unique and non-repeating" number—for instance, as a trigger for a platform like Zapier—all you really need is the numeric format of the date itself:

=VALUE(A2)

...copied down as needed.

Either formula can work as an array formula (i.e., placed in one cell and filling the entire column) with minor changes:

=ArrayFormula(IF(A2:A = "", "", VALUE(TEXT(INT(((A2:A*COS(DAY(A2:A))*PI())-INT(A2:A*COS(DAY(A2:A))*PI()))*1000)+IF(INT(((A2:A*COS(DAY(A2:A))*PI())-INT(A2:A*COS(DAY(A2:A))*PI()))*1000)<100,WEEKDAY(A2:A)*100,0),"000")))

=ArrayFormula(IF(A2:A = "", "", VALUE(A2:A))

Related Topic