# How can I generate a Random Number that does not change in 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`?

If you are happy with your generated data, you can replace your formulae with the values that were generated.

2. Copy
3. Right click -> "Paste special" -> "Paste values only"

The formulae will now have been replaced by the values.
You can also paste with ctrl+shift+v.

Thanks to Jamie Evans for the copy/paste suggestion. My solution skips pasting into notepad. I would have just left a comment if I had >=50 points.

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

tools > script editor

Then make a function. You will need to hook the function up to a button or some other event.

In order to enter the number you need to make select a range. Then put the value in with and array of arrays. Think of it like an array of rows.

Here is my sample Google script

``````function generateNumbers () {
var sheet = ss.getSheets()[0];
var randNumberRows = []

var i;
for (i = 0; i < 20; i++) {
var row = []
row.push(randInt(-20,20));
row.push(randInt(-20,20));
row.push(randInt(-20,20));
randNumberRows.push(row);
}

var range = sheet.getRange("A2:C21");
range.setValues(randNumberRows);
}

function randInt(start, end) {
// range is from start to end + 1 so it is inclusive
r = end - start + 1
return Math.floor(Math.random() * r) + start;
}
``````

I have a very simple easy solution, two easy little workarounds:

• With Excel as well: Generate as many random numbers as you want in Excel, copy paste, done