Distributing a Cell Value

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

Guest

Please help! I have a column of values numbered 1-16. I then assign a value
to each number - the value would be how many times I want that number to
appear in a distribution. For example, if I assign the value "32" to the
number "2" and the value "12" to the number 4, I need a formula to randomly
plot the number 2, 32 times in a column, and the number 4, 12 times in the
same column. The column will have 100 rows.
 
Well, that was a tough one, but I think I've got an answer for you. It is
rather involved and I would be curious to know if anybody has a simpler
solution, but here goes...

I have set the problem up in a spreadsheet as follows:

Column A contains the integers you want to use:
A1: 1; A2: 2; A3: 3; ...; A14: 14; A14: 15; A14: 16

Column B contains the occurrences of the respective integers (I just made up
the occurrence values):
B1: 5; B2: 2; B3: 14; etc (16 values, adding up to a total of 100, since you
specified 100 rows)

Column C contains a list of random numbers between your lowest and highest
integers. For 100 total occurrences of 16 integers, you are probably safe to
generate 500 random numbers, so in each of the cells in C1:C500, use the
following formula:
=RANDBETWEEN($A$1,$A$16)

Column D contains a copy of Column C, filtering out occurrences of integers
beyong the limit set in Column B (D1 is simply set to C1, since the first
value will always be used):
D1: =C1
D2: =IF(COUNTIF($D$1:D1,C2)<INDEX($B$1:$B$16,C2),C2,"")
then copy cell D2 to D3:D500

Column E contains the row numbers of the (non-blank) values to display in
the final result. (E1 is simply set to 1, since the first value will always
be used):
E1: 1
E2: =E1+MATCH(TRUE,OFFSET($D$1,E1,0,500-E1,1)<>"",0)
(this formula must be "array-entered"; use ctrl-shift-enter when entering
the formula)
then copy cell E2 to E3:E100

Column F contains your results, looking up values in Column D based on the
row number in Column E (F1 is simply set to 1, since the first value will
always be used):
F1: =D1
F2: =INDEX($D$1:$D$500,E2)
then copy cell F2 to F3:F100

I have tested this on a random number of occurences (totaling 100) for the
integers 1 through 16 and it appears to work ok. If it doesn't work for you,
or you are having problems interpreting all this, feel free to send me an
email at ryanpoth-AT-hotmail-DOT-com (replacing the "AT" and "DOT" with "@"
and ".") and I'll send you the spreadsheet.

HTH,
Ryan
 
Here's just another formulas play which may provide some ideas ..

It allows up to 3 values within A1:A16
to be distributed randomly within H1:H100
by the number of times indicated in col B

A sample construct is available at:
http://www.savefile.com/files/9980487
Randomizing values in a col by a fixed number of times.xls

In C1: =IF(B1="","",B1+ROW()/10^10)

In D1:
=IF(ISERROR(SMALL($C:$C,ROW())),"",
INDEX(A:A,MATCH(SMALL($C:$C,ROW()),$C:$C,0)))
Copy D1 to E1

Select C1:E1, fill down to E16

C1:C16 acts as an arb tie-breaker col,
in case identical values are entered in col B

Cols D and E extracts neatly the 3 values in col A
and corresponding times to repeat in col B to the top

In F1:
=IF(ROW()>$E$1,
IF(ROW()>SUM($E$1:$E$2),
IF(ROW()>SUM($E$1:$E$3),"",
OFFSET($D$3,INT((ROW()-ROW())/$E$3),)),
OFFSET($D$2,INT((ROW()-ROW())/$E$2),)),
OFFSET($D$1,INT((ROW()-1)/$E$1),))

In G1: =RAND()
In H1: =INDEX(F:F,RANK(G1,$G$1:$G$100))

Select F1:H1, fill down to H100

F1:F100 simply returns a col of the numbers within D1:D3,
repeated by the number of times indicated in E1:E3

(I stopped at 3 nested IF levels, but the formula in F1 can be
extended a little further to accommodate more levels in a like
manner, ref: the formula structure in F1 above which is
intentionally sliced into different lines for clarity)

G1:G100 provides the randomization "base"
H1:H100 returns a random shuffle of F1:100,
which yields the desired results

Just press F9 to regenerate afresh
the random distribution in H1:H100
 
Here's a revised formulas set up which now allows *all* 16 values within
A1:A16 to be distributed randomly within K1:K100 by the number of times
indicated in col B.

Revised sample construct available at:
http://www.savefile.com/files/8028542
RandomizingColValuesByFixedNoOfTimes_v2.xls

In C1: =IF(B1="","",B1+ROW()/10^10)

In D1:
=IF(ISERROR(SMALL($C:$C,ROW())),"",
INDEX(A:A,MATCH(SMALL($C:$C,ROW()),$C:$C,0)))
Copy D1 to E1

Select C1:E1, fill down to E16

C1:C16 acts as an arb tie-breaker col,
in case identical values are specified in col B

Cols D and E extracts neatly the 3 values in col A
and corresponding times to repeat in col B to the top

(above as before, no change)

In B17: =IF(SUM(B1:B16)>100,">100, re-do!","")
(B17 is just to provide a visual check
that the total in B1:B16 does not exceed the number of cells in K1:K100)

In F1:
=
IF(ROW()>$E$1,
IF(ROW()>SUM($E$1:$E$2),
IF(ROW()>SUM($E$1:$E$3),
IF(ROW()>SUM($E$1:$E$4),
IF(ROW()>SUM($E$1:$E$5),G1,
OFFSET($D$5,INT((ROW()-ROW())/$E$5),)),
OFFSET($D$4,INT((ROW()-ROW())/$E$4),)),
OFFSET($D$3,INT((ROW()-ROW())/$E$3),)),
OFFSET($D$2,INT((ROW()-ROW())/$E$2),)),
OFFSET($D$1,INT((ROW()-1)/$E$1),))

In G1:
=
IF(ROW()>SUM($E$1:$E$6),
IF(ROW()>SUM($E$1:$E$7),
IF(ROW()>SUM($E$1:$E$8),
IF(ROW()>SUM($E$1:$E$9),
IF(ROW()>SUM($E$1:$E$10),H1,
OFFSET($D$10,INT((ROW()-ROW())/$E$10),)),
OFFSET($D$9,INT((ROW()-ROW())/$E$9),)),
OFFSET($D$8,INT((ROW()-ROW())/$E$8),)),
OFFSET($D$7,INT((ROW()-ROW())/$E$7),)),
OFFSET($D$6,INT((ROW()-ROW())/$E$6),))

In H1:
=
IF(ROW()>SUM($E$1:$E$11),
IF(ROW()>SUM($E$1:$E$12),
IF(ROW()>SUM($E$1:$E$13),
IF(ROW()>SUM($E$1:$E$14),
IF(ROW()>SUM($E$1:$E$15),I1,
OFFSET($D$15,INT((ROW()-ROW())/$E$15),)),
OFFSET($D$14,INT((ROW()-ROW())/$E$14),)),
OFFSET($D$13,INT((ROW()-ROW())/$E$13),)),
OFFSET($D$12,INT((ROW()-ROW())/$E$12),)),
OFFSET($D$11,INT((ROW()-ROW())/$E$11),))

In I1:
=IF(ROW()>SUM($E$1:$E$16),"",
OFFSET($D$16,INT((ROW()-ROW())/$E$16),))

In J1: =RAND()
In K1: =INDEX(F:F,RANK(G1,$G$1:$G$100))

Select F1:K1, fill down to K100

F1:F100 returns a col of the numbers within D1:D16,
repeated by the number of times indicated in E1:E16
(The subsequent IFs in cols G, H, I covers the full range
and auto-"collapses" back to col F)

J1:J100 provides the randomization "base"
K1:K100 returns a random shuffle of F1:F100,
which yields the desired results

Just press F9 to regenerate afresh
the random distribution in K1:K100

---
 
Oops, sorry, documentation typo in line:
In K1: =INDEX(F:F,RANK(G1,$G$1:$G$100))
Click to expand...

Should have read as:
In K1: =INDEX(F:F,RANK(J1,$J$1:$J$100))
Click to expand...

The revised sample file provided earlier
has the correct formula implemented in col K

---
 
Back
Top