How to implement weighted random selection in Google Sheets based on rarity ranking

I am working on building a tool to help with my DND campaigns that will randomly give me a set number of items from the master list of the game. Needed some help wrapping my head around weighted random selection for this instance:

Column A is a list of 61 items. All of the items are assigned a rarity ranking of 1-4(1 Common, 2 Uncommon, 3 Rare, 4 Legendary), found in column B.

I need a random selection among the 61 items that is weighted according to their rarity. What also needs to be taken into consideration is that many different items share the same rarity level.

What Google Sheets formula or additional steps would I need to take? Any alternatives to reach the same result?

I've tried this formula:

=INDEX(DM_MasterList!B4:B61, MATCH(RANDBETWEEN(1, SUM(DM_MasterList!AS4:AS61)), DM_MasterList!AS4:AS61, 1))

Where B4:B61 is the item name, J4:J61 is the rarity, and AS4:AS61 is the weighted probability using the formula: 1-(B4-1)/10.

This didn't seem to work, as it was only returning the last value of the list.

Solution

Here is a discussion which includes a result (the Norman Gray answer) which though somewhat esoteric does seem to have a good provenance if you look back through the references. Basically you compute

-log(r)/w

where r is a random number between 0 and 1 i.e. rand() and divide it by the associated weight to get a value that you can sort on, then take the smallest n values as your sample set where n is the number of items to be selected.

Applying this to your situation would give

=ArrayFormula(ARRAY_CONSTRAIN(SORT({$B11:$B21,-LOG(RANDARRAY(11))/(1-(C11:C21-1)/10)},2,1),3,1))