![]() You can then link this list in with your table. This will give you a list of numbers, which are all equally weighted, that total up to 100. So every number only appeared once and will not. Then in B1 and dragging down, put the following formula: =SMALL($A$1:$A$10,ROW()+1)-SMALL($A$1:$A$10,ROW()) For example, you could generate random numbers between 1 and 10 and list the sequence in an Excel range. The first column now contains a list of unique numbers in random order. Since this column is random, the sort order applied to the first column will be completely random. The last step is to apply filters to both columns and sort the column that contains the random numbers. Below your list, let's say A10, put =100. Step 3: Sort the column of random numbers. So in your case, put this formula in column A of your sheet, starting at A2 and dragging down: =RANDBETWEEN(0,100) (1) pick nine random numbers between (2) put the nine numbers, a zero, and a 1000, in an array (3) sort the array (4) using subtraction get the ten "distances" between array values. ![]() My recommendation is that you use the algorithm proposed by Joe Blow in that linked question, which is as follows: Only once you have determined which algorithm you want, can you actually code this in Excel. For example, suppose I want to generate 10 random numbers between 1 and 100. While not truly random (as nothing is), the easiest way would be to make another column, and use the RAND() function to add a number for each name. Never use Excel anymore for making combinations. ![]() You need to determine what you want out of your answers - do you want each of the rows in your table to be weighted the same as each other row? With a total limit of 1-100, do you want the first value to have an equal chance at 99 and 20? You can now also generate combinations with 3 items per combination with one list of items. As shown in this similar PHP question: How can I create a specified amount of random values that all equal up to a specified number in PHP?, as soon as you limit any random number based on the numbers around it, you are impacting the "true randomness" in some way. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |