Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Placement whilst fitting constraints
Hi,
Is it possible for excel in a selected set of cells, to place a letter (A, B, C or D) in each of the cells "randomly" so that at the end there is a total of 10 A's for example, 8B's, 9C's and 1D... overall? Also, whenever anything is randomly generated in Excel, is there a way to stop the values refreshing to new values everytime the user clicks something? Any help would be most appreciated, Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Placement whilst fitting constraints
One way:
In A1:A27, enter your As,Bs,Cs and D. In B1:B27 enter =RAND(). When you want to get a random assortment, choose Data/Sort, and sort A1:B27 on column B. This will create a new set of RAND()s so you can sort again if you wish. If you need a different configuration, you can use INDEX() to grab values from the values in column A. In article , realmfighter wrote: Hi, Is it possible for excel in a selected set of cells, to place a letter (A, B, C or D) in each of the cells "randomly" so that at the end there is a total of 10 A's for example, 8B's, 9C's and 1D... overall? Also, whenever anything is randomly generated in Excel, is there a way to stop the values refreshing to new values everytime the user clicks something? Any help would be most appreciated, Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Placement whilst fitting constraints
Is it possible for excel in a selected set of cells, to place a letter (A,
B, C or D) in each of the cells "randomly" so that at the end there is a total of 10 A's for example, 8B's, 9C's and 1D... overall? I am newly returned to Excel after a LONG absence, so I am having to relearn everything about Excel from scratch. With that said, let me preface my posting by saying the odds are this is NOT the solution you should follow.<g Okay, I am going to put your random characters in the A column and use two "helper" columns (C and D) to complete the calculation process. Put a string of characters in D1 representing the letter distribution you want. That is, for your stated requirements, put AAAAAAAAAABBBBBBBBCCCCCCCCCD in D1 (the order is unimportant as the RAND function will handle the mixing, but putting them in order makes proofing the string easier). Put this formula in A1... =MID(D1,C1,1) and copy it down for a total of as many cells as there are letters in D1 (hence, for your string of 28 letters, you will end up in row 28). Put this formula in C1... =INT(LEN(D1)*RAND())+1 and copy it down to the last row that you copied the previous formula to (that is, row 28). Finally, put this formula in D2 (note I said D2)... =REPLACE(D1,C1,1,"") and copy it down to the last row that you copied the previous formula to (again, row 28). Also, whenever anything is randomly generated in Excel, is there a way to stop the values refreshing to new values everytime the user clicks something? I don't know the answer to this one... but (obviously) you can use the clicking into the formula bar of a filled cell followed by the <Enter key to generate a new rearrangement of your letters in column A. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
top row appear whilst scrolling | Excel Discussion (Misc queries) | |||
Page setup whilst in Group mode | Excel Discussion (Misc queries) | |||
Name Placement | Excel Discussion (Misc queries) | |||
Highlighting cells whilst editing | Excel Discussion (Misc queries) | |||
How can I run a macro in the background whilst a UserForm is visib | Excel Discussion (Misc queries) |