Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Data Generator
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Data Generator
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. -- ** John C ** "Bill" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Data Generator
data 1 is only 5% of what? data 2 is 12% of what?
Bill wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Data Generator
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. -- Gary''s Student - gsnu200814 "Bill" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Data Generator
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" wrote: 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. -- ** John C ** "Bill" wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Data Generator
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 "Bob I" wrote: data 1 is only 5% of what? data 2 is 12% of what? Bill wrote: 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Data Generator
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)? -- ** John C ** "Bill" wrote: 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" wrote: 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. -- ** John C ** "Bill" wrote: 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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Data Generator
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" wrote: 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)? -- ** John C ** "Bill" wrote: 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" wrote: 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. -- ** John C ** "Bill" wrote: 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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Data Generator
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) -- ** John C ** "Bill" wrote: 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" wrote: 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)? -- ** John C ** "Bill" wrote: 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" wrote: 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. -- ** John C ** "Bill" wrote: 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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Data Generator
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" wrote: 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) -- ** John C ** "Bill" wrote: 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" wrote: 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)? -- ** John C ** "Bill" wrote: 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" wrote: 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. -- ** John C ** "Bill" wrote: 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? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Data Generator
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 a 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 D5:D10, 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. -- ** John C ** "Bill" wrote: 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" wrote: 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) -- ** John C ** "Bill" wrote: 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" wrote: 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)? -- ** John C ** "Bill" wrote: 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" wrote: 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. -- ** John C ** "Bill" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random number Generator | New Users to Excel | |||
Random Generator | Excel Discussion (Misc queries) | |||
Random Name Generator | Excel Worksheet Functions | |||
Random cell generator | Excel Worksheet Functions | |||
Random # Generator | Excel Worksheet Functions |