How to take in account frequancy with random selection

Copper Contributor

Hi I want select element from a list randomly but respecting some frequency value.

So I have 5 element: A=(45, 12, 6, 2, 1) and the related frequency; B=(0.02, 0.08, 0.30, 0.35, 0.35) respectively. I want to fill a column with 800 rows with the element took from A respecting their frequency in B. I started by multiplying the frequency for the rows, so I have the count of how many element of each class I must have: C=(16, 64, 240, 280, 280).

Now my idea is to count how many "45" I have in my random column and say that if they are more than 16 I have to change some numbers, and so on with the other value until the correct count.

But I have no idea of how to do it.


I start typing something like this,but how to complete it?

IF(COUNTIF(O2:O802,"45")>C2, ...)


Immagine 2023-01-18 100054.jpg


1 Reply

@Lucamuraz I count to 880 rows when using your sequence of 16, 64, 240, 280, 280. Assumed you wanted the first 280 to be 200. Just guessing though.


Then I used Power Query to quickly generate a list of 800 numbers with that frequency. Loaded it back to Excel and added a manual column with random numbers that I then sorted in ascending order. Just as an example, though.


The first column then gives you a list of 800 randomized numbers with the correct frequency for each of the 5 numbers (45, 12, 6, 2, 1).


Is that what you had in mind?