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

[WhatTheF]

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
	A	B	C	D
1	Total Number of Animals	Species	Percentage of Probibility	Results
2	34	Virginia Opossum	65.00%
3		Fox Squirrel	7.00%
4		Red Fox	2.00%
5		Gray Fox	2.00%
6		Raccoons	16.00%
7		Coyotes	1.00%
8		Skunks	6.00%
9		Bobcat	1.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!

 

[StephenCrump]

Welcome to the Forum!

How about:

	A	B	C	D	E
1	Total	Species	Probability	Cumulative	Results
2	34	Virginia Opossum	65%	0%	20
3		Fox Squirrel	7%	65%	2
4		Red Fox	2%	72%	0
5		Gray Fox	2%	74%	2
6		Raccoons	16%	76%	6
7		Coyotes	1%	92%	2
8		Skunks	6%	93%	2
9		Bobcat	1%	99%	0
10
Sheet1

Cell Formulas
Range	Formula
E2:E9	E2	=MMULT(--(XLOOKUP(RANDARRAY(,A2),D2:D9,B2:B9,,-1)=B2:B9),SEQUENCE(A2,,,0))
D3:D9	D3	=D2+C2
Dynamic array formulas.

 

[WhatTheF]

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.

 

[StephenCrump]

No problem. Here's how you could list the animals:

	A	B	C	D
1	Total	Species	Probability	Cumulative
2	20	Virginia Opossum	65%	0%
3		Fox Squirrel	7%	65%
4		Red Fox	2%	72%
5		Gray Fox	2%	74%
6		Raccoons	16%	76%
7		Coyotes	1%	92%
8		Skunks	6%	93%
9		Bobcat	1%	99%
10
11		List
12		Raccoons	Virginia Opossum	16
13		Virginia Opossum	Fox Squirrel	0
14		Virginia Opossum	Red Fox	0
15		Virginia Opossum	Gray Fox	0
16		Virginia Opossum	Raccoons	2
17		Virginia Opossum	Coyotes	1
18		Virginia Opossum	Skunks	1
19		Raccoons	Bobcat	0
20		Virginia Opossum
21		Virginia Opossum
22		Virginia Opossum
23		Virginia Opossum
24		Virginia Opossum
25		Virginia Opossum
26		Skunks
27		Coyotes
28		Virginia Opossum
29		Virginia Opossum
30		Virginia Opossum
31		Virginia Opossum
32
Sheet1

Cell Formulas
Range	Formula
D3:D9	D3	=D2+C2
B12:B31	B12	=XLOOKUP(RANDARRAY(A2),D2:D9,B2:B9,,-1)
C12:C19	C12	=B2:B9
D12:D19	D12	=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
	A	B	C	D	E	F
1	Total	Species	Probability	Cumulative	Sample #	Sample %
2	100,000	Virginia Opossum	65%	0%	64,996	65.0%
3		Fox Squirrel	7%	65%	6,999	7.0%
4		Red Fox	2%	72%	2,054	2.1%
5		Gray Fox	2%	74%	1,914	1.9%
6		Raccoons	16%	76%	16,000	16.0%
7		Coyotes	1%	92%	994	1.0%
8		Skunks	6%	93%	6,003	6.0%
9		Bobcat	1%	99%	1,040	1.0%
10
11					100,000
Sheet1

Cell Formulas
Range	Formula
E2:E9	E2	=MMULT(--(XLOOKUP(RANDARRAY(,A2),D2:D9,B2:B9,,-1)=B2:B9),SEQUENCE(A2,,,0))
D3:D9	D3	=D2+C2
F2:F9	F2	=E2/A$2
E11	E11	=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.