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.

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.

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