How to Create Randomness in Smartsheet

Summary: David Tutwiler, David Tutwiler, shared a conversation with Sarah Stone about creating randomness in Smartsheet. Since Smartsheet lacks a native RANDOM function, Sarah devised a custom function using prime numbers. David shared this function and encouraged others to support the idea of adding a native Random function to Smartsheet by voting on a community suggestion. He also invited others to share their experiences with using RANDOM functions in Smartsheet.

AI Summary

Sarah Stone reached out and was unfortunately not able to share in this meeting. However, we did share a fun conversation about Randomness in Smartsheet.

Because Smartsheet does not have a RANDOM function native to the platform, you have to make one yourself. Sarah used the following function, which uses prime numbers and seems to work well:

=IFERROR(INDEX({Dictionary All Words}, MOD((Today@row - DATE(YEAR(Today@row), 1, 1)) * 73 + 19, COUNTIF({Dictionary IDs}, <>"")) + 1), "")

Here is a community article talking about the same thing - Link

And here is a community suggestion where you can Up Vote the idea for Smartsheet to add a Random function. Go vote if you have a use-case for this - Link

Has anyone else needed to use a RANDOM function like this in Smartsheet?

1 comment

Sarah Stone

1 month, 1 week ago

Thanks for sharing this, David!

I was thinking over the formula more today, and I realized my current formula would restart every year. While that's probably fine for most players, I think the formula below would be better, as it should cycle through the whole dictionary.

=INDEX({Dictionary All Words},MOD((Today@row - DATE(2020, 1, 1)) * 73 + 19,COUNT({Dictionary IDs})) + 1)

I swapped that formula out, and I tweaked an automation that wasn't properly moving yesterday's guesses to the archive sheet.

Open to any feedback on the "random" formula or anything else in the workspace!