How to =Random() with Weighted Probability?

Showcase
1

(tldr:) Goal: create a button/formula that outputs a random single result based on a table with corresponding pool of list and its equivalent column of probability weight of random chance getting picked (either by percentage or number)

Need help…
I just want to generate a random single entry from a list (accounting by percentage or by number) every time a button is click. Prefer direct to page formula. But if it cannot be, one can use a control element or intermediary table.

(This embedded document below, doesnt account the weight, and formula is kinda bug or im wrong.)

  • Making use of RandomItem() and RandomInteger() might be handy too.
  • There might be a need of two formulas, for list with percentile weight, and for list with numerical weight.

Cheers community

2

Hey there @chrisquim !

There is likely a more elegant solution to this, but as Im watching Land Before Time with my son, this is the idea that came to my head. See screenshots below:

image
image1876×1128 190 KB

Here is the formula for the white button:

image
image1286×926 91.1 KB

Your green button is running three actions:

  1. Pressing the white button in your main table (which adds a certain amount of rows to the helper table based on the probability percentage. For example, the number 112 has a 60% probability, so 6 rows are added to the helper table with 112 as an input, whereas 184 has 10% probability so it is only adding 1 row to the helper table for 184)
  2. The green button then selects a randomitem() from the list of all numbers in the helper table and inputs it into the results table
  3. The green button then deletes all rows in the helper table so it is ready for a new run.

Hopefully that is what you were looking for! If you are looking for something different let me know.

I would have embedded a doc, but my computer is deciding to not work properly today, so here is the link to the document if you want to see → Document

1 Like
3

Hi @chrisquim,

Please, have a look at this implementation:

Basically, I let the Item “popularity” to be consistent with the desired weight, and you pick from a list that is populated with that cardinality.
Also I added few checks to see how it is behaving towards the expected distribution.

I hope this helps.
Cheers!

1 Like
4

This is a simple way to choose a random item from a table. I use an automation to run.

ModifyRows(@rowToStoreValue, 
  @rowToStoreValue.value, 
  RoundUp(
    Product(tableOfItemsToRandomlyChoose.Count(), Random()),
    0
  )
)
1 Like
5

Hi @Johg_Ananda,
this is definitely useful.

However, I guess that the point was on weighting the items, so that they have different probability to be picked up.

Cheers!

1 Like
6

hello… thanks and nice representation of the answer…
however, how about circumstances that need to account the percentage probability up to decimal places? lets say upto 2 or 3 decimal places (or any max decimal places allowed)?

it appears that in your current suggested implementation, picking from the pool list of “Item Popularity”, it can only count up to whole integer of percent weight (ie 10%, 30%, 60%)… In circumstances example weights like “53.85%,15.38%,30.77%”, the decimal places will get ignored/truncated in the count.

One solution could be increase/introduce a base 10^x multiplier (to arbitrarily move the decimal place upon item calculation in order to account it). However, it would exponentially increase the items in “item popularity” list… increasing document size or backend processing? hmmm

7

Hi @chrisquim,
I totally understand your point and this makes sense.

If you need a viable solution that covers most of your use-cases, then this workaround can be reliable and can be optimised as such.
For example, do you need a high precision distribution over a high number of iterations?
This could drive where to put the most demanding logic implementation.

Instead, if you need a scalable framework to deal with weighted random probability and/or other distribution strategies, then perhaps Coda isn’t the proper answer.

Understanding your actual need and what kind of cardinality you are planning to work with in terms of items, generations and precision would help to drive the solution.

Cheers!

3 Likes
8

Here’s another solution that works with arbitrary weights (could be easily adapted to use percentages instead). It relies on two extra columns in the list of items to choose from: a column for sort order (I just used RowID, but it could be any unique number) and a column for the accumulated weight in that sort order (which I called Range because I couldn’t think of a better name).

By the way, that Range column could slow down your doc if you have thousands of rows and you are constantly changing the weights or adding new rows. You could make a button to calculate those ranges if you need more control, and/or if the weights never change for a given row, and you will only ever add rows, you could replace the column formula with a Value for new rows formula.

2 Likes
9

@Ryan_Martens2 instead of using a Filter to find and sum all previous rows, you could create a PreviousRow column, and that might make this faster.

Current Approach

Current formula in Range:

thisRow.Weight+thisTable.Filter(
  CurrentValue.[Row ID / Sort Order] < thisRow.[Row ID / Sort Order]
).Weight.Sum()

PrevRow Approach

PrevRow based formula in Range:

thisRow.Weight + thisRow.PrevRow.Range.IfBlank(0)

PrevRow is a column with a formula like:

If(thisRow.[Row ID / Sort Order] - 1 != 0,
  thisTable.Nth(thisRow.[Row ID / Sort Order] -1),
  ''
)

Also, for Row Id / Sort Order make sure to use

thisTable.Find(thisRow)

Since it will allow you to manually reorder rows as you please.

Ok tested, yeah it works

1 Like