help-need to randomly spread a # over a range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a predetermined # that I need to spread over a certain # of cells in a range of cells. For instance I have a range of 15 cells in a row, I have the number 4 that I need to spread over these cells, but I need it done randomly and also the sum of the column once randomly spread cannot exceed a sum of 50.
 
Ben,

The last condition of not exceeding 50 will be challenging.

The random part can be handled by doing something like, if(rand()<0.2, 4,0).
So now if the randomly created number is less than 0.2, 4 is entered. But
now you want to see if the column adds to less than 50. You can't create a
circular loop. So you must look at the preceding cells and total those
numbers up to see if it is less than 50.

For example, let's say you are using A1:A15.

In cell A14, the formula was, if(sum(A1:A13)>=50,0, if(rand()<0.2, 4,0)).
The problem with this formula is that things are no longer random. The last
few cells in the row have a larger chance of being zero than do the first 12
cells (12*4=48, which is less than 50). Thus your desire for randomness is
broken.

Anyway, I hope this provides some food for thought. Perhaps there is
another method where you could randomly "turn off" certain cells if the
total value exceeded 50?

Good luck.

Regards,
Kevin



Ben said:
I have a predetermined # that I need to spread over a certain # of cells
Click to expand...
in a range of cells. For instance I have a range of 15 cells in a row, I
have the number 4 that I need to spread over these cells, but I need it done
randomly and also the sum of the column once randomly spread cannot exceed a
sum of 50.
 
Assuming A1:O1 is the range you need to fill:-

In A2:O2 put =RAND()

In A3 put =RANK(A2,$A$2:$O$2) and copy across to O3

In A5 put your predetermined number

In B5 put =INT(50/A5)

Hit F9 repeatedly to cycle through various random spreads. Change the number in A5 and it
will all be automatic, capping the number of entries to maintain the restriction of a max
of 50.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Ben said:
I have a predetermined # that I need to spread over a certain # of cells in a range of
Click to expand...
cells. For instance I have a range of 15 cells in a row, I have the number 4 that I need
to spread over these cells, but I need it done randomly and also the sum of the column
once randomly spread cannot exceed a sum of 50.
 
Dohhhh - Might help if I give you the last part of it :-)

In A1 put the following and then copy across to O1

=IF(A3<=$B$5,$A$5,"")
 
Ben,

Ignore my stuff, and just read Ken's message. He's right on target!

Regards,
Kevin
 
Back
Top