Random Number - Generate sample from the raw data

Romano el Polako · 02/07/24

Hey Smart-Heads!

Desired goal is to be able to extract only 20% of the rows meeting specific criteria.

As far as I know SS, does not have RANDOM formula, neither allows to write 1-2-3-4-5, double click and have the same all the way down.

Any ideas / topics (from the past?), to solve this issue?

Thanks,

Romano

=Chris Palmer · 02/07/24

I was able to achieve the 1 2 3 4 5 repeated all the way down by experimenting with an "Auto Number" column.

This column simply counts all the rows in your sheet.

Using this formula in a new column =MOD([Auto Number]@row - 1, 5) + 1

Like so:

You can modify the MOD formula if you want seemingly random numbers as well.

Hope this helps!

=Chris Palmer · 02/07/24

I was able to achieve the 1 2 3 4 5 repeated all the way down by experimenting with an "Auto Number" column.

This column simply counts all the rows in your sheet.

Using this formula in a new column =MOD([Auto Number]@row - 1, 5) + 1

Like so:

You can modify the MOD formula if you want seemingly random numbers as well.

Hope this helps!

Romano el Polako · 02/08/24

Awesome, thank you very much, will give it a try!

@Update, works blyatifully, even on big numerals, cheers buddy!

Thanks,

Romano

=Chris Palmer · 02/08/24

Awesome, I'm glad this worked for you.

Best Answer