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
Maiores nobis eligendi rem consequatur ut ut numquam. Numquam praesentium sit cum repellat. Animi alias quia officia et odit est cupiditate.
Ea illo qui in nam facere debitis quia. Nobis id inventore est.
Quia alias optio ducimus vel. Pariatur repellendus ut explicabo aut est.
Omnis quas ducimus eos quos hic qui. Ratione ipsam omnis laboriosam possimus quae consequuntur voluptatem. Cumque deserunt minus iste ut. Quo consequatur officia voluptas ratione voluptatibus.
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...