3

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?

2

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

1

With a google script

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 ss = SpreadsheetApp.getActiveSpreadsheet();
  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;
}
1

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

  1. Mark your area
  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.

1

You can use the Google PowerTools sheet plugin to place random numbers in the range you have selected and they will not change.

0

I was looking into the same question tonight and found this paper: https://www.ssl.berkeley.edu/~mlampton/RandomSpreadsheet4.pdf

You can use the formulas in that document to create your own pseudo-random number generator in a few minutes. That will give you a list of random numbers that will only change when you change the seed.

0

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

  • Notepad: Generate in Google Sheets, copy paste into notepad, then copy paste back in, done

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.