Hi - Dave here.
Happy Friday!
An interesting problem in Excel is how to assign random groups to a set of data.
For example, in the worksheet below, the goal is to randomly assign a group of "A", "B", or "C" to each row in the data.
The formula in cell F5 looks like this:
=CHOOSE(RANDBETWEEN(1,3),"A","B","C")
As the formula is copied down it returns a random group ("A", "B", or "C") at each new row. The values to assign can be customized as you like.
[
Download the workbook and read the full explanation]
This formula will work in all versions of Excel. In the current version of Excel (Exel 2021 or later) it is possible to generate all random groups in one step by using the RANDARRAY function instead of the RANDBETWEEN function. It is also possible to use INDEX instead of CHOOSE, which is helpful when the group names appear in a range on the worksheet. The article above provides all formulas and a detailed explanation.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
