Random number, not repeated between 1000 and 9999

[Daniel_Robbins]

Perhaps I’m missing it, but how do I generate random numbers? I am missing “randbetween” and “rnd” from Excel.
Thanks for any and all help.

[Tyler_Kaye]

I would love to get a work around for this. I am trying to use a setup to generate automated SMS message for Zapier and need to use some random numbers to alternate the messages that are being sent.

[Benjamin_James_]

Here is my brute force solution! This formula takes a direct approach by converting the letters in RECORD_ID() to numbers. It’s just 52 nested SUBSTITUTE() calls :rofl:

SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(RIGHT(RECORD_ID(), 14)
,"a","0")
,"b","1")
,"c","2")
,"d","3")
,"e","4")
,"f","5")
,"g","6")
,"h","7")
,"i","8")
,"j","9")
,"k","0")
,"l","1")
,"m","2")
,"n","3")
,"o","4")
,"p","5")
,"q","6")
,"r","7")
,"s","8")
,"t","9")
,"u","0")
,"v","1")
,"w","2")
,"x","3")
,"y","4")
,"z","5")
,"A","6")
,"B","7")
,"C","8")
,"D","9")
,"E","0")
,"F","1")
,"G","2")
,"H","3")
,"I","4")
,"J","5")
,"K","6")
,"L","7")
,"M","8")
,"N","9")
,"O","0")
,"P","1")
,"Q","2")
,"R","3")
,"S","4")
,"T","5")
,"U","6")
,"V","7")
,"W","8")
,"X","9")
,"Y","0")
,"Z","1")

The digit distribution should be close to uniform, but because 26 * 2 = 56 letters is not divisible by 10, there will be a slight bias toward “0” and “1” corresponding to those last two characters, “Y” and “Z”.

[Patrick-Kennedy]

Mahmoud_Habib:

(VALUE(DATETIME_FORMAT(CREATED_TIME(), ‘0.smh’))*(MAX-MIN))+MIN

Very nicely done @Mahmoud_Habib!

[Joachim_Brindea]

In case anyone needs it, for example this is how to get a random number between 1 and 30:

IF(VALUE(RECORD_ID()) <= 0, 1, IF(VALUE(RECORD_ID()) >= 1000000, 30, MOD(VALUE(RECORD_ID()), 30) + 1))

[Herege]

Thanks Joachim. That works very nice and without much complications.

I need to generate a random number between 1 and 156 and I just replaced the 30 for 156 and Voila :D.

I'm not exactly sure how that works but looks good, Although I got a lot of 1's and 2's (I have around 280 records)

I was using this formula:

ROUND((VALUE(DATETIME_FORMAT(CREATED_TIME(), '0.smh'))*(156-1)),0)

but I had a very few "hits" for numbers higher than 100 not sure why.

Can you elaborate a little more on how this randomness is reached?

Much thanks 🙂

[epicmike2835]

I have checked and rechecked this it is saying it is an invalid formula

[Joachim_Brindea]

The VALUE(RECORD_ID()) attempts to convert the unique record id (which is alphanumeric) to a numeric value. In theory, since each record has a unique id, the numeric value derived from it would also be unique.

1. This part IF(VALUE(RECORD_ID()) <= 0, 1,... checks if the numeric conversion resulted in a number less than or equal to zero. If yes, it returns 1.

2. The second condition IF(VALUE(RECORD_ID()) >= 1000000, 30,... checks if the numeric conversion resulted in a number greater than or equal to 1 million. If yes, it returns 30.

3. If neither of the conditions is met, it uses MOD(VALUE(RECORD_ID()), 30) + 1, which calculates the remainder when the numeric value of Record_ID is divided by 30 and adds 1 to it.

The randomness in this formula does not come from a random function but rather from the unique numeric id converted from the unique alphanumeric id of each record, distributed across a range of 1 to 30. 

[Joachim_Brindea]

Use this :

MOD(
  VALUE(
    LEFT(
        DATETIME_FORMAT(
          CREATED_TIME(), 
          'X'
        ), 
        5
      ) 
    & 
      RIGHT(
        DATETIME_FORMAT(
          CREATED_TIME(), 
          'X'
        ), 
        5
      ) 
    & 
      MID(
        RECORD_ID(), 
        LEN(
          RECORD_ID()
        ), 
        1
      )
    , 
    3
  ), 
  500
) 
+ 500

[epicmike2835]

Worked thanks!

[hello_ipsally]

I used irrational numbers using prime numbers, then using MOD to limit range. Here's an example of random number between 0 - 3.

MOD(
    MID(
            REGEX_REPLACE(
                ((AUTONUMBER/17+AUTONUMBER/13)/7)&""
             ,"[.]","")
    ,4,1)
,4)

1. AUTONUMBER will always be a unique number
2. AUTONUMBER / 17 and AUTONUMBER / 13 gives irrational numbers, dividing it by 7 to add complexity
3. (AUTONUMBER/17+AUTONUMBER/13)/7)&"" concatenation turns it into a string
4. REGEX_REPLACE( __.______, "[.]","") removes the decimal place
5. MID( _____ , 4 , 1) extracts the 4th number on that string
6. MOD( _____, 4) returns the remainder after dividing by 4 giving a number range between 0 - 3
   • Optionally +value at the end to adjust the range
7. Change the MID values for another random number for the same record

Results are good, only issues every 1547 numbers (13*17*7) which is 0 which is fine, results are pretty evenly divided across 0-3 without being an exact split (387,396,266,261 - out of 1310 records).