VBA Help with Expanding data down a column and creating random data

[richter394]

Book1.xlsm

Attached is an quick example of what I want. There are a couple things I want to do.

So I have a userform that I input data into a text box in. This data is then pasted into cells C4 and C5 on the active worksheet when the userform is closed.

First Question: I want to generate a set of random data when the userform is closed. I only want the data to be generated when the userform is closed and not everytime I click something. For example my random values in Column A change, I want them to be generated once and then remain constant. Now it may be because of the use of the NORMINV and RAND function. Should I use the random number generator? Which leads to the next problem. (We will assume a Standard Deviation of 40 for the set of random numbers)

2nd Question: I want the number of random number to be equal to the value in cell C4. So if 75 is entered into the userform the data in column A will expand to generate 75 random values instead of 10. I also need the average and standard deviation calculations in H2 and H3 to be calculated with all 75 data points instead of 10.

3rd Question: Is it possible to capture the highest and lowest 30% of the data? Right now it will capture it simply because I selected 30% of the 10 random data values.

Thank you if you can help with any of this.

[richter394]

Book1.xlsm

Sorry, This is the correct attachment

[AlphaFrog]

This will generate Values in the cells in column A from your UserForm

Please Login or Register  to view this content.

Use these formulas on the worksheet:
Average =AVERAGE(A:A)
Stand Dev. =STDEV(A:A)
Top 30% Avg =SUMIF(A:A,">="&LARGE(A:A,INT(C4*30%)))/INT(C4*30%)
Bot 30% Avg =SUMIF(A:A,"<="&SMALL(A:A,INT(C4*30%)))/INT(C4*30%)