Help with Random Numbers

[shadestreet]

I have a column of with 4,500 cells.

I want the number in each of the cells to be randomly picked from
list of 10 choices, all predefined (20, 27, 40, 54, 80, 120, 150, 190
220, 296).

Can anyone help me with this? Thanks

 

[Domenic]

*I have a column of with 4,500 cells.

I want the number in each of the cells to be randomly picked from
list of 10 choices, all predefined (20, 27, 40, 54, 80, 120, 150
190, 220, 296).

Can anyone help me with this? Thanks. *

Hi Shadetree,

try,

=CHOOSE(ROUNDUP((RAND()*10),0),20,27,40,54,80,120,150,190,220,296)

Hope this helps

 

[shadestreet]

Thanks, that was a very efficient way to solve this problem...I think
have one more difficult though, can't quite seem how to solve this..
(I may post it in a new thread too, but might as well take a crack a
it here).

In column A I have 4,500 rows of cells, each with a number between
and 1,000.

In columns B through X I need to break up the corresponding value i
Column A in a random fashion. For example, if the value in Column A i
150, then maybe B is 0, C is 10, D is 0, E is 120, F is 20, all other
are zero.

Any ideas on how to do this efficiently

 

[Domenic]

*Thanks, that was a very efficient way to solve this problem...
think I have one more difficult though, can't quite seem how to solv
this... (I may post it in a new thread too, but might as well take
crack at it here).

In column A I have 4,500 rows of cells, each with a number between
and 1,000.

In columns B through X I need to break up the corresponding value i
Column A in a random fashion. For example, if the value in Column
is 150, then maybe B is 0, C is 10, D is 0, E is 120, F is 20, al
others are zero.

Any ideas on how to do this efficiently? *

I don't understand how you're breaking up the values

 

[shadestreet]

Thanks for response, let me clarify a bit.

In Cell A1 I will have a random number that was computed by the abov
solution. Lets assume that number is 80.

Now in cells B1 - X1 I need to break up the value of 80 in a rando
fashion. The rules:

the sum of B1 - X1 must be equal to cell A1 (in this case, 80).

The "parts" of cell A1 that are spread out across B1 - X1 must be whol
positive numbers (no fractions).

Therefore, some possible solutions in cells B1 - X1, assuming cell A
equals 80, would be:

B1 = 80, cells C1 - X1 equal 0

C1 = 10, G1 = 25, H1 = 3, K1 = 2, L1 = 20, X1 = 20 (all other cell
equal zero)

D1 = 42, R1 = 38 (all other cells equal zero)

Does that help at all? I am thinking this will require a somewha
involved macro/vba routine, but you guys always seem to find muc
easier ways of solving problems so I thought I would give it a sho
here before I waste hours writing this (that and I don't know how t
make macros yet....so I would first need to teach myself that).

Thaks for any help or suggestions

 

[Domenic]

You may be right. This may require VBA. Unfortunately, I have no rea
experiance with it so I can't be of any help. Sorry