random number generation with probability with given total number amount of numbers

[spbailey]

Im not sure if anyone will be able to help with this. But I have this table

Project Type	Labeled	Frequency
Minor	1	30%
Small	2	35%
Medium	3	20%
Large	4	10%
Major	5	5%

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I need to fill the columns listed project label with the numbers 1-5, but only up to the total projects. The first two rows I put numbers in to show what I mean. The number don't have to add up to the total, just the actual number of numbers does.. I hope this isn't too confusing. This is for a simulation project, and I have to do like 2000 replications. I have all the totals, just need the population of the project label columns. Been staring at this for like 5 hours today and can't figure it out. Please help me.

 

[spbailey]

realized i didn't add the second chart

Total Number of Projects	Project Label 1	Project Label 2	Project Label 3	Project Label 4	Project Label 5	Project Label 6	Project Label 7	Project Label 8	Project Label 9
3	5	4	1
5	1	2	5	3	1
7
2
5
4
0
7
3
3

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Melvin88]

Can you try rephrasing what you're looking for? I think I know what you need but your wording is very confusing.

 

[spbailey]

Can you try rephrasing what you're looking for? I think I know what you need but your wording is very confusing.

Yeah I know, Im sorry just hard to explain. In the second table I need to fill the project label columns with numbers similar to the ones I put in there manually. But they have to be randomly selected using the other table. The other table has the numbers 1-5 which are the numbers I need to pull from, and the frequency/probability of those numbers. The total number of porjects is just adding the number of number, like 5 4 1 = 3 and 1 2 5 3 1 = 5. I need the rest of the rows filled out with the random 1-5 numbers with weighted frequency, but to not have more numbers show up than the total.

P.S. I feel like that probably isn't going to help. lol

 

[spbailey]

bump for help?

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Project Type​	Labeled​	Frequency​	Helper​
2​	Minor​	1​	30%​	1​
3​	Small​	2​	35%​	31​
4​	Medium​	3​	20%​	66​
5​	Large​	4​	10%​	86​
6​	Major​	5​	5%​	96​
7​
8​
9​	Total Number of Projects​	Project Label 1​	Project Label 2​	Project Label 3​	Project Label 4​	Project Label 5​	Project Label 6​	Project Label 7​	Project Label 8​	Project Label 9​
10​	3​	3​	1​	2​
11​	5​	2​	2​	3​	1​	1​
12​	7​	5​	2​	1​	3​	2​	3​	3​
13​	2​	4​	4​
14​	5​	2​	1​	2​	1​	1​
15​	4​	1​	3​	4​	4​
16​	0​
17​	7​	5​	5​	2​	1​	1​	2​	1​
18​	3​	2​	1​	3​
19​	3​	1​	2​	2​

<tbody>
</tbody>

Formula in D2 copied down
=100*SUM(C$1:C1)+1

Formula in B10 copied across and down
=IF($A10>=COLUMNS($B10:B10),LOOKUP(RANDBETWEEN(1,100),$D$2:$D$6,$B$2:$B$6),"")

Hope this helps

M.

 

[spbailey]

Thank you very much for the response, I'm wondering if what I did would get me the same result, but ended up doing more work. It seems to be working, and I'm getting answers that look correct.

=IF($H5<1,"-",IF($H5>=1,LOOKUP(INT(RAND()*100),{0,30,65,85,95},{1,2,3,4,5}),"-"))


in each column going across the part that I changed to orange in the cell about, adds 1, (=1,=2...=9) so it leaves the cell blank once the project label number exceeds that total number of projects.

Looking at what you did, it seems like its doing the same thing, I just did it a harder way.

 

[Marcelo Branco]

Your LOOKUP seems OK to me.
Didn't understand the H5<1 and H5>=1 parts.,but if the formula is generating the results you want / need everything is right!

M.

 

[Marcelo Branco]

Thank you very much for the response, I'm wondering if what I did would get me the same result, but ended up doing more work. It seems to be working, and I'm getting answers that look correct.

=IF($H5<1,"-",IF($H5>=1,LOOKUP(INT(RAND()*100),{0,30,65,85,95},{1,2,3,4,5}),"-"))

About the LOOKUP part of your formula
INT(RAND()*100) generates number from 0 to 100, that is 101 possible numbers.

I think it should be
LOOKUP(INT(RAND()*99),{0,30,65,85,95},{1,2,3,4,5})

M.