Random Data Generator

[Bill]

I have a data set that I created a block grid and a random genereator for. I
would like to put a percentage in for each piece of data so that data 1 is
only 5%, data 2 is 12% etc... How do I assign those percentages?

 

[John C]

Data 1 is only 5%.... of what? Same with Data 2, what is it 12% of? What is
being randomized? A little more clarity, and what is happening, and what you
expect/want to happen would be helpful.

 

[Bob I]

data 1 is only 5% of what? data 2 is 12% of what?

 

[Gary''s Student]

A very easy way;

In A1 thru A5 enter data1
in A6 thru A17 enter data2

Continue until you reach 100% in A100

In B1 thru B100 enter:
=RAND()

then sort cols A & B by B

Any sample pulled from column A will satisfy the distribution in the average.

 

[Bill]

Data is the column on the left. Each block in the grid has a random
generator for the data. I would like to assign a MAX percentage for each
data point.
FB1
FB2
FB3 00 01 02 03 04 05
FB4 0
C1 1
C2 2
C3 3
C4 4
PO 5
PO1
PO2
PO3
CU1
CU2
CU3
CU4

 

[Bill]

Data is the column on the left. Each block in the grid has a random
generator for the data. I would like to assign a MAX percentage for each
data point.
FB1
FB2
FB3 00 01 02 03 04 05
FB4 0
C1 1
C2 2
C3 3
C4 4
PO 5
PO1
PO2
PO3
CU1
CU2
CU3
CU4

 

[John C]

From you other post:
The percentages are created by me. 022 is the column / row that the data
point appears in. The data set on the far left is text that I am randomly
assigning into the block and do not want more than certain percentages of
each data point

022 ... how is this a valid column/row?

I am going to try to rephrase the question...
You have, in the data you sent, 16 different data points, FB1, FB2, FB3,
FB4, C1, C2, C3, C4, PO, PO1, PO2, PO3, and CU1, CU2, CU3, CU4.
Then you have a grid (or table) that you want these data points to appear
in. But you want to ensure that these data points do not appear in th grid
more than a certain percentage of the time.
Question 1: How big is your 'grid' (table) in rows in columns? In other
words, how many total data points will you need filled
Question 2: The MAX percentage for each data point, is this a number that is
going to be static? Or will this number be randomized also for the different
data points? In either case, where in the information given thus far are you
assigning a MAX percentage (albeit not necessarily working)?

 

[Bill]

Your rephrase is correct. The % is static, set by me. The grid table is 6
block by 6 block with column headers 01,02,03,04,05,06 and the row header
0,1,2,3,4,5. I have not assigend the % yet, was just trying to give an
example.

 

[John C]

Ok. A few more questions:
1> Is your grid always going to be a 6x6 (i.e.: 36 total data points)
2> Say you assign 8.33% maximum occurrances for 1 point (3 occurrances), and
11.11% for another point (4 occurances) as a maximum. Does this also entail
that they have either an 8.33% chance of occurring, or an 11.11% chance of
occurring? To rephrase, it's on the 36th and final cell, they have both
occurred twice, do they have an equal chance of occurring a 3rd time? Or does
the 11.11 have just a slight edge on its chance of occurring?
3> When you add up your static percentages for all 16 data points, do they
equal 100%? (In other words, each data point is guaranteed to occur X number
of times)

 

[Bill]

First, thanks for the help. I did not think it was this involved, but that's
why I asking.

1. Yes, always 6x6
2. Equal chance
3. Static percentage will equal 100%, always

 

[John C]

It's involved, and that's fine.
2. If 3 is true, then my question is invalid.
If the static percentage is equal to 100%, that means that each data point
is going to occur exactly a certain number of times in the grid.

I am going to change terminology on you for a moment. Instead of giving a
max percentage, I will use max occurrances.
Say they are:
2, 3, 2, 2, 2, 2, 2, 2, 4, 2, 2, 2, 2, 3, 2, 2
This would actually be equal to:
(2+3+2+2+2+2+2+2+4+2+2+2+2+3+2+2)=36 / 36 = 100%

So, with that in mind, I assigned a number of occurrances to each data
point, with total occurrances equaling 36. This is how I have my data set up.
M1:M36 ... formatted as text, and I entered every possible column/row
reference: 010, 020, 030, 040, 050, 060, 011, 021, ... 045, 055, 065
N1:N36 =RAND()
O1:O36, this was a little manual, I copied all 16 data points in O1:O16,
then O17:O32, then I typed the 3rd occurrances of FB2, PO, and CU2 in
O33:O35, and the 4th and final occurrance of PO in O36.
There might be a better way to do that, but not off the top of my head.
I had the grid column headers in E4:J4, formatted as text
I had the grid row headers in D510, formatted as text
In 011 (cell E5)
=VLOOKUP(E$4&$D5,$M$1:$O$36,3,FALSE)
This should fill the grid in order just as I typed in the O column. Then,
all you need to do to randomize it is press F9 (for a new set of random
numbers), then do a Data-->Sort on N1:O36, sort on N column (ascending,
descending, doesn't matter), and your grid will change appropriately.