[Updated October, 2015]
I have used Excel extensively over the last 10 years, and often find myself scratching my head wondering why Salesforce does not provide some of the most basic functions that I’ve grown accustomed to using in that tool for years. This week’s bane has been the lack of a random number generator.
So, how does one create their own random number generator?
As it turns out, generating a pseudo-random number generator isn’t too difficult–sort of. Not too difficult, that is, if you can read complex mathematical equations! (And “pseudo” because they are numbers derived from a computation rather than some physical phenomenon that is expected to be random, like space noise.)
According to Wikipedia there are several computational methods in use,and according to me (from my one-day of research) the Linear Congruential Generator method appears to be the most common. Of course this could be in part because the computation is the only one that is decipherable without an advanced mathematics degree!
The equation looks like this: Xn = ( Xo * a + c) mod m
Or in slightly mo-betta English: number = (seed_number * multiplier + increment) mod modulus
In order for this formula to work the multiplier and increment constants must be greater than 0 and less than the modulus (0 < constant < m), and the “seed” number must be greater than or equal to zero and less than the modulus (0 <= Xo < m).
Generating a Number Between Two Given Numbers
Now what if you want a random number between two specified numbers, like the RANDBETWEEN function in Excel? Well, luckily there is a straightforward calculation for this:
RandBetween = LowNum + (HiNum – LowNum) * RandNum
But there’s a “gotcha:” In order for the RandBetween equation to work RandNum must be between 0 and 1; however, the Linear Congruential Equation usually returns an integer value much greater than 1 (the MOD function returns the decimal portion of the result as a large integer). How do you convert this large number into a decimal number between 0 and 1?
Converting a Large Number to a Number Between 0 and 1
In order to convert a large number to a value between 0 and 1 you have to divide it by a number equal to or larger than the number itself. So in order to convert the result of the Linear Congruential Equation we need to figure out what the largest number that would be returned by the equation, and use that as our divisor.
Fortunately, as luck would have it, by definition the modulus (m) will always be larger than the generated random number so we can use it to convert our large number to one that falls between 0 and 1! This leaves the final equation looking like this:
Xn = [ ( Xo * a + c) mod m ] / m
Components of the Random Number Generator
So here are the basic components for my Random Number Generator:
- SeedNum is the seed
- HighNum and LowNum both have a default value of -9898. If the flow doesn’t receive a number that is different from this, the random number is not restricted
- RandomNumber and NextSeed are returned to the calling Flow
- RandomNumber is the number between HighNum and LowNum (if they are provided)
- NextSeed is the unrestricted random number
- Precision is the number of decimal places for the final result (Max value of 8)
- TempNum is used to switch HighNum and LowNum if they happen to be input in reverse order
What Constants Should be Used in the Equation?
Now what to use as the constants? According to one resource a minimal standard number generator would have the following constants:
- multiplier = 16807
- increment = 0
- modulus = 2147483647
The Seed Number
The seed number poses a bit of a problem. This algorithm is designed to produce a sequence of random numbers with the seed for the next random number being the result from the prior random number after you feed it an original seed. But what should the original seed be?
The most logical seed is the NOW() function which returns a date/time value. To convert that value to a number you have to subtract the “original date/time” value, or:
NumberValue = NOW() – $System.OriginDateTime
But in actuality, pretty much any number can be used, and if a SeedNum is not provided it has a default value of the above equation, but its default value could just as well be 0 or 1.
Finally, for the Linear Congruential Equation to be successful when multiple random numbers are needed, the result from the first iteration through the equation should be the seed for the next iteration, and so on. In other words, the original seed can be any value you like, but the subsequent seeds should be generated by the flow.
The RandomNum formula calculates the next random number:
The NumBtwn0and1 formula converts it to a number between 0 and 1:
The CalcRandBtwn formula finishes off the calculation if HighNum and LowNum are input into the flow:
The Final Flow
I added a couple final touches to the flow: one to compensate for a negative seed (which I handled by using the ABS function); and another to switch HighNum and LowNum if they happen to be input in reverse order.
And the final flow:
So there you have it.