3

I'm struggling with rand() and randbetween().

This is what I'm looking for: I open my spreadsheet, it generates 20 differents seeds (some between 1 and 12, some between 1 and 6, some between 1 and 3 and some between 1 and 10000) and keep them the same until I close.

When I open again, new numbers are generated and kept the same until I close. Doing manually (Ctrl+C, Ctrl+V) would be a nightmare, because I want to expand in the future to more than 100 different seeds.

A button assigned to a script that automates all the Ctrl+C, Ctrl+V in the beginning could work.

Any ideas?

Thanks!

1 Answer 1

3

If you define a LAMBDA and call RAND/RANDBETWEEN as the value for a parameter within it, the called value does not change when the sheet is edited, unlike if you call either of these functions directly. So one way of achieving something like what you want would be to call a LAMBDA if a tickbox is ticked (which you could do once upon opening the sheet). As a toy example:

  1. Place a tickbox in A1
  2. Place the following formula in B1:
=LAMBDA(x,if(A1,x,))(RAND())

Upon ticking A1, a random number will be generated in B1 and will remain until the box is unticked. You could scale this idea up as required. If you keep the box ticked across sheet openings, my [limited] experimentation suggests that on the first occasion the value changes, but on subsequent occasions it remains static.

1
  • Wow! This was exactly the solution I needed. Thank you so much! Never heard of this lambda function before. Since I needed different seeds, with different checkboxes, I've made one checkbox for each rand. To avoid excessive clicking, I've put a "main" checkbox on A1 and made every checkbox to be the same true/false state as this main one. So, with one tick, all checkboxes go on/off at once! You're awesome! Commented Sep 26, 2022 at 1:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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