Creating Random Numbers in SharePoint Calculated Columns
One of my examples for tonight's Cincinnati SharePoint User Group meeting[1]! See you there!
I wanted to add a "motivational" message to a list of new sales. To be "fair" (i.e. I did not want to think and create a good algorithm!) I wanted the messages to be random. Something like this:
But… Calculated Columns do not support the Excel RAND() or RANDBETWEEN() functions.
So, how to get a random number???
Calculated columns do support the =Now() function. This returns a numeric value that represents the current date and time. If formatted as a Date, or Date and Time, then you will see the current date. But, if you format it as Single Line of Text you will see something like: 42,691.3977137731, or a few seconds later: 42,691.3983521875. The last number starts to look like a random number! And if accurate, it changes every .0000000001 of a day, or about every 0.00000864 seconds. Close enough for me.
Get a random number between 0 and 9.
This one looks easy, just pull off the last digit from NOW()!
=RIGHT( NOW() ,1)
But.. there's one flaw with this… The last digit of a fractional value is never zero! (I.e. you will never see .111111110 unless custom formatted.)
So we need to pull off the next to last digit!
=LEFT( RIGHT( NOW() ,2) ,1 )
Get a random number between 1 and 5
With just a little math we can limit the range a bit. As we don't want the zero value we can skip the LEFT function for this one.
=ROUND( RIGHT( NOW()) / 2+0.5 ,0)
Here's a sample:
Get a random number between 0 and 999.
If you need bigger numbers, just return more digits:
=RIGHT(NOW(),3)
As RIGHT creates a string (text), you will get leading zeros ("012"). To remove the leading zeros just do some math!
= 0 + RIGHT(NOW(),3)
But… (there's always a "but"), this will never return a value that ends with a zero. So… back to the LEFT function:
=LEFT( RIGHT(NOW(),4), 3)
I.e. get the left three of the right four digits…
Random Messages?
This little exercise started out to create random messages. All we need to do is combine a random number with the CHOOSE function. As CHOOSE starts with item 1 and not item 0, we will need to add one to the random number.
=CHOOSE( LEFT( RIGHT( NOW() ,2), 1) + 1, "Good Job", "Wow!", "Good Work", "Thanks!", "Could be better",
"Gold star for you!", "a free coffee for you!",":-)", "You are the MAX!","Do it again!" )
Notes
- These are not guaranteed to be mathematically pure random numbers!
- The values depend on the exact instant that an item is added to a list and will change with each edit. (But will not change with each view.)
.
References
- ^ Cincinnati SharePoint User Group meeting (meetu.ps)
