Excel problem
I want to generate random numbers in order to do a simulation.For example, let's say we have 20 cats 80 dogs. How can i have different combinations of those numbers? The function "rand" can generate 0-1 for x times.But I do not know how to generate only 20 "0" and only 80 "1"
I'm not sure if I understand exactly what you want to do, but couldn't yo do something like this:
Where A1 =RANDBETWEEN(0,100)
And B1 = 100-A1
This is actually non-trivial and an interesting question. I am sure there are better ways of doing this but:
Fill 100 columns and 100 rows (all 10,000 cells) with "=if(rand()0.8,0,1)" Fill row 101 with sums of above 100 cells Use index function to find column in which the first 20 appears in the 101st row In Column 101, use offset function, with argument from above, to read the values from that particular column
That will get you an answer in probably 80% of the time. If you want it in more % of the time, increase number of columns or decrease number of rows/specifications.
Hope that helps. I feel like I am missing a very obvious solution.
key here is that you want to preserve the probability ratio of selection. if you create a variable that returns 0 or 1 equally, then it simulates a population where you have 50 cats and 50 dogs. rand will give you what you need, you just have to deal with the output accordingly.
to get a random integer from 1 to 10, use =ROUND(RAND()*9,0)+1
then assign your outputs accordingly 1-8 = dogs, 9-10 = cats
=IF(ROUND(RAND()*9,0)+1=8,"Dog","Cat")
Better yet - to actually get results 100% of the time, pick 100 random numbers (in Column A), then in column B find the rank of each number in Column A (rank amongst all the numbers in Column A). Then in Column C, if the value in Column B is = 20, the Cat, otherwise Dog.
Of course, its not that easy. There is no function to rank a number in Excel, so either write one in VBA or synthetically replicate. Replicating is a bit tricky, though possible. Will require 200 columns of calculations - basically in each subsequent column, find the max of the previous column, and then eliminate that number from the set, and move the set over to the next column. Then use match function to determine at which point a number dropped out, and divide by 2. Done.
ah, missed the combination bit. agree with dr joe that rand and rank would be the easiest solution.
put 20 cats and 80 dogs in cells A1:A100
put =RAND() in cells B1:B100
insert VBA module
run this subroutine
Sub rand_n_rank() Range("B1:B100").Calculate Range("A1:B100").Sort Range("B1"), xlAscending End Sub
Soluta quod est iste ad exercitationem consequuntur. Et libero aperiam nemo qui quisquam debitis. Consequatur est quisquam hic voluptatem vel laboriosam. Quo enim harum similique nulla modi optio autem. Sed harum dolores tempore doloribus.
Quae quis et id odit doloribus amet ipsum. Reprehenderit accusamus magni error sit quam sunt.
Odit accusantium esse atque totam aliquam eius. Fugit delectus illum unde architecto. Possimus qui dicta et aperiam. Ea dolor distinctio sint error cumque. Expedita doloremque voluptas esse eaque. Delectus magnam et placeat cumque blanditiis omnis sunt.
Itaque temporibus consequatur qui est magni. Ab quod aperiam ut est rerum ut. Quas vel in voluptas laboriosam nihil a. Temporibus nesciunt mollitia rerum nobis aperiam illum atque. Sint dicta ut mollitia hic ea molestiae vel quo.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...