Help

Re: Random number functions

Solved
Jump to Solution
1051 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Daniel_Robbins
6 - Interface Innovator
6 - Interface Innovator

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.

39 Replies 39

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_
4 - Data Explorer
4 - Data Explorer

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”.

random from recordid

Very nicely done @Mahmoud_Habib!

Joachim_Brindea
6 - Interface Innovator
6 - Interface Innovator

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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 🙂

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

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. 

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
4 - Data Explorer
4 - Data Explorer

Worked thanks!

hello_ipsally
4 - Data Explorer
4 - Data Explorer

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

hello_ipsally_0-1718403984118.png