How to get random drawings from a list of people that have percentage weighted on them

I have a list of employees I want to randomly select. I also have them weighted - meaning some will be at a higher rate of getting chosen.

I need an expression for this?

Or maybe this has multiple expressions in different places?

I believe we only have the RANDBETWEEN() function within AppSheet to randomly choose numbers.

I think what I would do is assign a list of numbers to each person based on their weighting (maybe randomly). E.g. weight of 1 is 1 number, 2 is 2 numbers, etc.

Count the total numbers assigned and then perform a RANDBETWEEN() 1 and that count. The chosen person is the one who has the generated number in their list.

2 Likes

I agree. I have a risk factor table. It basically sets up what you just said:

Risk Factor 0 = employee will not be in random drawing
Risk Factor 1 = Employee will have one entry
Risk Factor 2 = Employee will have 2 entries
etc…

However - how do i write the expressions?

I am understanding that you wish to perform a drawing to chose a “winning” employee at some point in time. Because things can change that affect the drawing over time - employee count and/or assignment of Risk Factor, you really can’t assign the numbers until you actually perform the drawing.

The total numbers you assign are based on SUM(<<All employee Risk Factors>>

I would add a column to hold the list of assigned numbers. Then create a set of actions to run at drawing time. First action(s), to make this easy, would just go down the list of employees and assign a sequential list of numbers.

Employee 1 Risk Factor=1 - assign the number list of {1}
Employee 2 Risk Factor=3 - assign the list of {2, 3, 4}
Employee 3 Risk Factor=2 - assign list of {5, 6}
Employee 4 Risk Factor=1 - assign list of {7}
…and so on

Once you have assigned the numbers from 1 to SUM(<<All employee Risk Factors>>

Then you can perform your drawing by randomly selecting one of the numbers with this expression:

RANDBETWEEN(1, SUM(<<All employee Risk Factors>>)

RANDBETWEEN() is an inclusive random function which means it can return the lower bound (in this case 1) and it can return the upper-bound (SUM(<<All employee Risk Factors>>)

The “winning” employee is the one with the drawn number in their list.

2 Likes

You gave a great answer, I am afraid i am just lost a little. So I already have a list of employees - would this be same as your "SUM(<>)

COUNT(
FILTER(
Nestle Log Results,
([Last Name] <> “Last Name”,([Last Name]))))

Next,
“Add a column to hold the list of assigned numbers”? - is that to be virtual?

Next,
First action - “go down the list of employees and assign a sequential list of numbers” - what would that expression look like?

Last,
How do i perform the drawing?

To maybe make this make more sense to me:

I have 396 total employees right now. How do i assign those 396 per each employee with their risk factor ( i have a column already giving me this number per employee)

That row is [Number of entries]

I am not understanding how to take that and give that employee the right amount of numbers for the drawing - Meaning

Employee 1 has 2 entries so he gets - {1,2}

Not knowing your data I’m trying to give you just the general approach which is why I am lax on details. If someone else has n easier way, please chime in!!

It doesn’t seem so. The SUM() is a sum of the “Number of Entries” across all employees that would participate in the Drawing so something like this:

SUM(SELECT(Employees[Number of Entries], true)) – add criteris if needed to filter out any employee rows

No, I believe this should be a normal column. You don’t want these values recomputed every Sync between drawings.

Again, i don’t know your data so this might be tricky. I would create, with a set of actions, a looping ability to cycle through the employees list assigning the the proper list of numbers. You can refer to @Steve 's article below on how to do this. Of course, you will need to modify it somewhat to fit your use case. Instead of adding rows, you will be updating a list of numbers on an already existing row and column. But the same looping concept still applies.

This is the easy part. Once all Employee numbers have been assigned, you simply need to execute the RANDBETWEEN() expression from before.

How you do this is up to you. You could just run it as part of the group of actions, identify the winning Employee, mark that row as the winner (somehow) and use a Format Rule to highlight the row as the winner.

You could display an action in the app for someone to tap, that then selects the winner as described above. Where and how is up to you.

2 Likes

So you are saying in order to get the sequential numbers - i need to create action buttons?

Reading through what Steve created was a bit confusing. But it looks like he is allowing the user to quickly add rows to your spreadsheet inside the app. How, or better yet, what would i change to NOT make this add rows, but to add the proper amount of numbers to the row PER employee?

So something that would take:

Sum of total entries = SUM(employees X risk factors)
Then take that SUM and distribute numbers per employee according to how many each one is suppose to get based on their (Risk Factor)

Quick example of my current data:

I have 396 employees
I have 988 entries (i got this by multiplying 396 X risk factor per employee)

So now i simply need the app to count down starting at 988 or count up to 988 - distributing numbers to each employee. If Employee A has a risk factor of 3 - he needs to get numbers {1,2,3}. This is where i am completely lost. Steve has a great article on adding rows - but i need to add numbers to a row and then move on to the next row without duplicating any of the numbers.

I’ll think on it to see if there an easier way to simulate the physical assigning of numbers.

My thought is that maybe we can programmatically determine which user WOULD HAVE been assigned the random number without actually assigning numbers? I am not sure and it certainly is not coming to me at the moment.

Thank you. I am just trying to understand it as well. I know the numbers but i cannot figure out how to take (988) and distribute that number into the list without duplicating a number AND giving multiple numbers to those who have multipliers.

Tried this - no deal :frowning:
IF(
[Number of Entries] = “4”,
RANDBETWEEN(1, COUNT(Nestle Log Results[Total Employees entering Random drawing]) *4),

IF(
[Number of Entries] = “3”,
RANDBETWEEN(1, [Total Employees entering Random drawing]),

IF(
[Number of Entries] = “2”,
RANDBETWEEN(1, [Total Employees entering Random drawing]),

IF(
[Number of Entries] = “1”,
RANDBETWEEN(1, [Total Employees entering Random drawing]),

“0”))))

It assigns numbers but not eh right amount of numbers:

Example - if employee 1 is Risk factor 2 - he should get 2 numbers.