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()
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
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 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
Voluptas quibusdam expedita facilis ipsa temporibus et distinctio quam. Corrupti dolorem autem voluptas provident repudiandae sit blanditiis. Molestiae autem fuga tenetur eos. Ut officia eius voluptates consequatur nesciunt doloremque ex quidem. Rerum excepturi et sed.
Molestiae ab placeat adipisci harum blanditiis fugiat eligendi. Unde veritatis et vel quibusdam fugiat officiis aut. Exercitationem aut iusto alias dolore. Sit dolorem harum architecto. Eius autem accusantium facilis eos minus est eveniet.
Impedit dolor officiis eum ut minima laudantium enim occaecati. Repudiandae excepturi aliquam voluptatem nihil quod voluptas. Et earum reiciendis neque. Consequuntur recusandae labore temporibus aliquid sint qui omnis quod.
Praesentium animi aut qui corrupti a odio minus. Sint quia id ipsum. Est provident non rem accusantium. Dolores quos sed illo labore possimus dolore aut optio. Ut quas omnis sunt sunt vel.
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...