Randomly Pick contracts for reviews

Charles Osborn

Hello,

I am working on a process that will help our providers due peer reviews. I have built a Blueprint that makes the review sheets with all the information they need. Need help thinking of a way to determine who the Reviewer should be. Basically I have a list of all the Providers and need to put one of them in my intake list as the reviewer. It can be random, but can't be the same person as the Reviewee. Anyone do something like this before?

Thanks,

Jason Tarpinian

RAND() would be a nice feature, but I thought of something that maybe works?

RowID: is the system column, used to initiate the upcoming formulas using a unique value.

Random String: =RowID@row * 12 + "D" … this formula takes the RowID and makes it more different… I don't know how to explain it other than it doesn't work on the next step unless you do this.

HEXTODEC: =HEXTODEC([Random String]@row) … I honestly have no idea what HEXTODEC is, and the Smartsheet help doesn't do much, but what I do know is that it looks like it generates a fairly random number. Again, maybe somebody can confirm what it actually does, but it seems random enough to me.

Reviewer #: =MOD(HEXTODEC@row, 5) + 1 … MOD returns the remainder after dividing a number. 5 would be the number of possible reviewers, the +1 is for when there is not a remainder returned.

User Name: =INDEX(COLLECT([Reviewer Lookup]:[Reviewer Lookup], [Reviewer # Lookup]:[Reviewer # Lookup], [Reviewer #]@row, [Reviewer Lookup]:[Reviewer Lookup], <>Reviewee@row), 1) … this formula can then use the seemingly random "Reviewer #" to lookup against the table of the "N" number of reviewers possible.

The 2 columns on the right are just the lookup tables. Each reviewer would need to be listed twice with a different lookup number in each table, in case the formula in "User Name" returns the "Reviewee"s name, without the duplicate table the formula returns an #ERROR.

Jgorsich

This situation is somewhat similar:

Trying to set up a Round Robin for techs based on project type and if they are not out on leave — Smartsheet Community

Smartsheet still doesn't have any actual random number generator functions - but you COULD do a round robin with your reviewee excluded from the choices.

Jason Tarpinian

RAND() would be a nice feature, but I thought of something that maybe works?

RowID: is the system column, used to initiate the upcoming formulas using a unique value.

Random String: =RowID@row * 12 + "D" … this formula takes the RowID and makes it more different… I don't know how to explain it other than it doesn't work on the next step unless you do this.

HEXTODEC: =HEXTODEC([Random String]@row) … I honestly have no idea what HEXTODEC is, and the Smartsheet help doesn't do much, but what I do know is that it looks like it generates a fairly random number. Again, maybe somebody can confirm what it actually does, but it seems random enough to me.

Reviewer #: =MOD(HEXTODEC@row, 5) + 1 … MOD returns the remainder after dividing a number. 5 would be the number of possible reviewers, the +1 is for when there is not a remainder returned.

User Name: =INDEX(COLLECT([Reviewer Lookup]:[Reviewer Lookup], [Reviewer # Lookup]:[Reviewer # Lookup], [Reviewer #]@row, [Reviewer Lookup]:[Reviewer Lookup], <>Reviewee@row), 1) … this formula can then use the seemingly random "Reviewer #" to lookup against the table of the "N" number of reviewers possible.

The 2 columns on the right are just the lookup tables. Each reviewer would need to be listed twice with a different lookup number in each table, in case the formula in "User Name" returns the "Reviewee"s name, without the duplicate table the formula returns an #ERROR.

Jgorsich

@Jason Tarpinian HEXTODEC translates a hexadecimal value (base 16) to a decimal value (base 10). The D is needed in the first formula to make the values kinda look like hexadecimal values. You'd get by with any letter A-F.

Hexadecimal - Wikipedia

In most current use cases, the letters A–F or a–f represent the values 10–15, while the numerals 0–9 are used to represent their decimal values.

The problem with this is that if you have an even number of reviewers, it won't work at all - some reviewers will get nothing for every even number, and for certain even numbers (like 16) 1 reviewer will get all of them. (It works pretty well as long as the divisor in the mod() formula is odd though.)

You'll sometimes see similar things done with the date or time (here is a thread talking about using the created and edited columns for granular timestamps: Is there a way to access the current time? — Smartsheet Community combined with Extracting the Time from the timestamp — Smartsheet Community

Here, for example, you could add a Created column and then use

=MID(TIME(SUBSTITUTE(Created@row, DATEONLY(Created@row), ""), 1, 3), 4, 2)

to get a number representing the minutes in the hour, this would give you a "random" value between 0 and 59. If you needed more, you could determine the minutes since the start of the day. You could further randomize this by doing the same thing for an entry some arbitrary distance away and taking the time between them.

But, just to be clear and incase someone in the future finds this thread needing a random value for something like statistical analysis, neither value is actually random - the first is 100% dictated by the row number and will always fall in the same order and the second is 100% dictated by the time of column creation and subject to rapid duplication if data is entered quickly (unless you complicate it by referencing additional time stamps - but still not truly random).

In this particular case, and presuming that an even number of reviewers is possible, I'd personally go with a round robin assignment.

Smartsheet REALLY needs a rand() function.

Jason Tarpinian

Thanks for confirming, @Jgorsich! I thought it might be too good to be true. Seconded for let's get a RAND() function!

Jgorsich

@Jason Tarpinian - for what is is worth, I was testing your method and had written out a whole "it ain't random, but boy it actually really works for this application!" reply before I'd realized I'd only tried odd numbers, lol. It works beautifully for odd numbers, actually results in a more even distribution than a truly random selection!