How to generate a random number in Google Sheets / Excel through a discrete list of percentage of influence in random outcome?

Question

Let's say I'm randomly picking up a number 1, 2, 3, and I take notes of how many times they were picked out of 10 times I did this. After this experiment, and taking the notes of the percentage of the times these numbers were picked in this 10 randomly generated picks, I want to randomly pick a number but this time having the weight of the percentage of times that I just took note from the original procedure.

For instance, if 3 was picked 20% of times, then the random generator tool will have it 20% of the times in consideration instead of going equally ~33% for each number 1,2 and 3.

The thing I'm missing is if there is any way to (either in Excel or Google Sheets) give this "weight" of the percentages a random picker.

Answer 1

to generate 10 numbers from fixed set (1, 2, 3) you can use:

=INDEX(ROUND(RANDARRAY(10)*(3-1))+1)

if this gives you distribution like:

1
2
1
2
1
2
3
2
3
1

where number 3 is picked up 20% of times you can find out the distribution like:

=INDEX(QUERY({A2:A11, COUNTIFS(A2:A11, A2:A11)}, 
 "select Col1,count(Col2)/10 group by Col1 label count(Col2)/10''"))

now to assign a weight we can reuse it like:

=INDEX(ROUND(RANDARRAY(10)*(MAX(A2:A11)-A2:A11))+MIN(A2:A11))

where you can notice that the % distribution of number 3 is always significantly lower or none:

for more precision and to avoid ghost values you can use:

=INDEX(SORTN(SORT(FLATTEN(SPLIT(QUERY(REPT(SORT(UNIQUE(A2:A11))&"×", 
 QUERY({A2:A11, COUNTIFS(A2:A11, A2:A11)}, 
 "select count(Col2)*10 group by Col1 label count(Col2)*10''")),,9^9), 
 "×"))), 10, 1, RANDARRAY(100), 1))

if you wish to freeze the random generation follow the white fox into the forest of ice