Creating Random Numbers in SharePoint Calculated Columns

 

One of my examples for tonight’s Cincinnati SharePoint User Group meeting! 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.)

.