Random selection based on percentage of possibility with a given quantity...

WhatTheF

New Member
Joined
Feb 25, 2025
Messages
4
Office Version
  1. 2021
Platform
  1. Windows
I suuuure am glad places like this and people like YOU exist!!!

In case the title doesn't give it away, I'm lost as to how to even ask my question so I DON'T have to bother everyone that knows what they're talking about! But, alas, here we are...

I need create a formula (or something...) so I can take a list of items and generate a random list from it based on an assigned percentage of each item. over a given number of cells. I'm going to try to break this out in a way that might actually make sense to someone other than me!

Let say I have a list of animals, Possum, Squirrel, Red Fox, Gray Fox, Raccoon, Coyote, Skunk, and Bobcat. There is a set number of animals that I need to populate a list of XX. In my example, there should be 34, but this number changes frequently. Of those 34 animals, I need 65% to be Possum, 7% to be Fox Squirrel, 2% to be Red Fox, 2% to be Gray Fox, 16% to be Raccoon, 1% to be Coyote, 6% to be Skunk, and 1% to be Bobcat.

How do I create a formula/code that I can populate this list into column D on my attached Mini Sheet?

Report Informatino Generation.xlsx
ABCD
1Total Number of AnimalsSpeciesPercentage of ProbibilityResults
234Virginia Opossum65.00%
3Fox Squirrel7.00%
4Red Fox2.00%
5Gray Fox2.00%
6Raccoons16.00%
7Coyotes1.00%
8Skunks6.00%
9Bobcat1.00%
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Sheet2


(Clear as mud, right?!?)

Thank you!
 
Welcome to the Forum!

How about:

ABCDE
1TotalSpeciesProbabilityCumulativeResults
234Virginia Opossum65%0%20
3Fox Squirrel7%65%2
4Red Fox2%72%0
5Gray Fox2%74%2
6Raccoons16%76%6
7Coyotes1%92%2
8Skunks6%93%2
9Bobcat1%99%0
10
Sheet1
Cell Formulas
RangeFormula
E2:E9E2=MMULT(--(XLOOKUP(RANDARRAY(,A2),D2:D9,B2:B9,,-1)=B2:B9),SEQUENCE(A2,,,0))
D3:D9D3=D2+C2
Dynamic array formulas.
 
Upvote 0
I'm sorry for not being more clear. I would need the formula/code to actually list the animals out in a random order. In other words, I would Column E to be 34 rows with "Possum" listed 20 times, "Squirrel" twice, "Gray Fox" twice, "Raccoon" six times, "Coyote" twice, and "Skunk" twice, but in a random order.

I'm also having an issue with the numbers this way. The margin of error in the calculation seems pretty excessive. I know this is a math issue, but I'm trying to get my results to match fairly closely with the applies percentages. Meaning, I'd rather have 0 for the 1% of the Coyote and 2% Gray Fox, while having 4 for the 6% and 7% of the Skunk and Squirrel. The reason being, we are fare more likely to see two extra raccoons than two coyotes or two gray foxes.

This is a being used as a planning guide for a wildlife rehabilitation facility. The percentages that will go into the final workbook will be aggregated through daily intake records over the past 14 years, then averaged to get weekly, monthly, and quarterly intake forecasts.
 
Upvote 0
No problem. Here's how you could list the animals:

ABCD
1TotalSpeciesProbabilityCumulative
220Virginia Opossum65%0%
3Fox Squirrel7%65%
4Red Fox2%72%
5Gray Fox2%74%
6Raccoons16%76%
7Coyotes1%92%
8Skunks6%93%
9Bobcat1%99%
10
11List
12RaccoonsVirginia Opossum16
13Virginia OpossumFox Squirrel0
14Virginia OpossumRed Fox0
15Virginia OpossumGray Fox0
16Virginia OpossumRaccoons2
17Virginia OpossumCoyotes1
18Virginia OpossumSkunks1
19RaccoonsBobcat0
20Virginia Opossum
21Virginia Opossum
22Virginia Opossum
23Virginia Opossum
24Virginia Opossum
25Virginia Opossum
26Skunks
27Coyotes
28Virginia Opossum
29Virginia Opossum
30Virginia Opossum
31Virginia Opossum
32
Sheet1
Cell Formulas
RangeFormula
D3:D9D3=D2+C2
B12:B31B12=XLOOKUP(RANDARRAY(A2),D2:D9,B2:B9,,-1)
C12:C19C12=B2:B9
D12:D19D12=COUNTIF(B12#,C12#)
Dynamic array formulas.


The margin of error in the calculation seems pretty excessive. I know this is a math issue ....
Yes, it's do with sample size. The bigger the number, the closer you expect the randomised results to be to expected:

Book5
ABCDEF
1TotalSpeciesProbabilityCumulativeSample #Sample %
2100,000Virginia Opossum65%0%64,99665.0%
3Fox Squirrel7%65%6,9997.0%
4Red Fox2%72%2,0542.1%
5Gray Fox2%74%1,9141.9%
6Raccoons16%76%16,00016.0%
7Coyotes1%92%9941.0%
8Skunks6%93%6,0036.0%
9Bobcat1%99%1,0401.0%
10
11100,000
Sheet1
Cell Formulas
RangeFormula
E2:E9E2=MMULT(--(XLOOKUP(RANDARRAY(,A2),D2:D9,B2:B9,,-1)=B2:B9),SEQUENCE(A2,,,0))
D3:D9D3=D2+C2
F2:F9F2=E2/A$2
E11E11=SUM(E2#)
Dynamic array formulas.


The smaller the size, the more variability you'd expect. For example, with a sample size of 1, it's possible (albeit unlikely) that the animal seen is a bobcat or coyote. But run 100,000 iterations and the distributions will converge as in the single random sample of 100,000 shown above.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top