vendredi 28 mai 2021

In Google Sheets how can I randomize the order of a set of values?

Maybe I'm missing a keyword in my searches for a solution, but I didn't find what I'm looking for. In Google Sheets I want to take a set of numbers and reorder it randomly. For example, start with the set [1,2,3,4] and get back [4,2,1,3]. Any ideas which function or a combination of functions may achieve this goal?

The entire process that I want to achieve is something like this: I have a set of 4 fields. Their sum is fixed. I want to assign them randomized values. So, I was thinking to iterate through this process:

  • Create a random integer between 0 and the max possible value (in the first iteration it's the fixed sum)
  • The new max value is the last max value minus the new random number.
  • Check if the new max is zero.
    • If not:
      • Return to the 1st step and repeat - This goes on until there are four values
      • If needed the 4th value shall be increased so the total will match the fixed sum.
    • Else, continue.
  • Randomize the order of the 4 values.
  • Assign the values to the 4 fields.



Aucun commentaire:

Enregistrer un commentaire