Create random sampling with table calcs (tiers, Excel RANK function) (Community)

View the original community article here

Last tested: Sep 27, 2019
 

Let's say you have an explore, and you want a table calc that spits out 1,2,3,4,5 in a pattern (ie, every 5th row will be 5, the row after 1, then 2, etc).

You can use this as a way of emulating random sampling, and it's fairly trivial to do in Excel with the RANK() function.

We can do this with by nesting some table calcs!

if(

contains(

to_string(row()/5), ".2"), 1,

if(

contains(to_string(row()/5),".4"),2,

if(

contains(to_string(row()/5), ".6"),3,

if(

contains(to_string(row()/5), ".8"),4

,5 ))))

We can extend this out for as many (or fewer) 'tiers' the user wants in their table.

At a high level, we can always predict the decimal output of a whole_number / x. 1/5 will always end in .2, 2/5 will always end in .4, 3/5 in .6, etc. This works with any combination of whole_number and x (1/3 = .33, 2/3 = .66, etc).

The table calc just grabs the row number, divides by number of rows in the pattern, then searches for the decimal we know should be in the pattern at that position.