MirageSF
asked on
Random Number Generator, where average equals a defined value
I need a sheet that can generate 5 cols, op1, op2, op3, op4 and op5 by 50 rows of random numbers between the values defined by cells A1 and B1. So, basically will generate 250 random numbers. However the total average of all these random numbers MUST be equal to the value defined in C1, so if C1 contains 14.5 then the entire average of those 250 numbers must also equal 14.5.
Also the COL average for the 50 numbers below each of the OP’s must be within a 5% tolerance of the value in C1, so if C1 was 14.5 then the 50 numbers must have an total average that falls within 13.78 and 15.23, but the 50 individual values must fall within the range defined in A1 and B1.
A1 is likely to be between 8.0 and 12.9 and B1 would be 10.0 to 19.9 so range is 8.0 to 19.9
Now the final challenge, we need to create a histogram of these 250 values, which is basically 16 bins showing the frequency of the numbers, the graph must resemble steps going up and then down, with the peak roughly in the middle, some minor fluctuations are acceptable.
Also the COL average for the 50 numbers below each of the OP’s must be within a 5% tolerance of the value in C1, so if C1 was 14.5 then the 50 numbers must have an total average that falls within 13.78 and 15.23, but the 50 individual values must fall within the range defined in A1 and B1.
A1 is likely to be between 8.0 and 12.9 and B1 would be 10.0 to 19.9 so range is 8.0 to 19.9
Now the final challenge, we need to create a histogram of these 250 values, which is basically 16 bins showing the frequency of the numbers, the graph must resemble steps going up and then down, with the peak roughly in the middle, some minor fluctuations are acceptable.
ASKER
Hi Barman,
Thank you for looking at this for me.
The random data in the 5 columns, i.e. the 250 numbers. Are used to generate the table for the histogram so that it would be normal distribution, to do this.
RANDMIN = The lowest value of the 250
RANDMAX = The maximum value of the 250 values
BINS = SqRoot of 250 rounded so 16
RANGE=RANDMAX - RANDMIN
BINRANGE=RANGE/BINS so 0.5625
Then we have column D running 1 - 16 (bins) down
With start of E1=Lowest value, F1=E1+BINRANGE
Then, next line down i.e. BIN 2, we have E2=F1+0.01 (as start point) and F2=E2+BINRANGE
and so on until we have covered all the 16 bins. The using the Frequency command we can see how many values in the random table fall between these values within each bin.
This then gives us a table to look at the frequency in which the RANDOM values fall, creating our table for the Histogram.
If your not sure what im blabbing on about fire me the code for random numbers over, and I will add to my sheet and post back.
Thanks
Thank you for looking at this for me.
The random data in the 5 columns, i.e. the 250 numbers. Are used to generate the table for the histogram so that it would be normal distribution, to do this.
RANDMIN = The lowest value of the 250
RANDMAX = The maximum value of the 250 values
BINS = SqRoot of 250 rounded so 16
RANGE=RANDMAX - RANDMIN
BINRANGE=RANGE/BINS so 0.5625
Then we have column D running 1 - 16 (bins) down
With start of E1=Lowest value, F1=E1+BINRANGE
Then, next line down i.e. BIN 2, we have E2=F1+0.01 (as start point) and F2=E2+BINRANGE
and so on until we have covered all the 16 bins. The using the Frequency command we can see how many values in the random table fall between these values within each bin.
This then gives us a table to look at the frequency in which the RANDOM values fall, creating our table for the Histogram.
If your not sure what im blabbing on about fire me the code for random numbers over, and I will add to my sheet and post back.
Thanks
Here is my first stab at answering the question based upon the question as asked.
See if it meets requirements or is wildly off track.
Click the button to use the TEMPLATE to generate a new fixed sheet of data meeting requirements.
and again: 250 random numbers will not distribute normally.
You need to take the average of the rows to get this to distribute normally.
Goal-Seek.xlsm
See if it meets requirements or is wildly off track.
Click the button to use the TEMPLATE to generate a new fixed sheet of data meeting requirements.
and again: 250 random numbers will not distribute normally.
You need to take the average of the rows to get this to distribute normally.
Goal-Seek.xlsm
ASKER
Barman,
Cheers, I have amended and added the required bit to create the histogram to the first tab, the reason it needs to be calculated this way is because once the data is generated and creates a 250 values that fall within the set guidelines, and it creates a histogram that appears normal distribution, the data values alone are taken and placed into another sheet which uses this method, so to remain compatible basically.
The sheet seems to generate values between 10-20 fine within the set average of 14.5, although when I changed these values it appeared to make no difference?
There appears to be 51 rows of numbers instead of 50.
In order for the histogram to appear normal deviation it maybe required to have some offset values from the defined MIN/MAX numbers, I tried using something along the lines of first 10 rows -4 from avg, next 10 -2 from average, next 10 0, next 10 +2 and final 10 +4, seemed to give a more varied range and make table more normal
Goal-Seek.xlsm
Cheers, I have amended and added the required bit to create the histogram to the first tab, the reason it needs to be calculated this way is because once the data is generated and creates a 250 values that fall within the set guidelines, and it creates a histogram that appears normal distribution, the data values alone are taken and placed into another sheet which uses this method, so to remain compatible basically.
The sheet seems to generate values between 10-20 fine within the set average of 14.5, although when I changed these values it appeared to make no difference?
There appears to be 51 rows of numbers instead of 50.
In order for the histogram to appear normal deviation it maybe required to have some offset values from the defined MIN/MAX numbers, I tried using something along the lines of first 10 rows -4 from avg, next 10 -2 from average, next 10 0, next 10 +2 and final 10 +4, seemed to give a more varied range and make table more normal
Goal-Seek.xlsm
You must change the TEMPLATE sheet to change the values.
The macro will just run through the permiatations that the sheet generates so the less likely the outcome, the longer it will take to stumble upon it.
And yes, misscount by me, 51 rows.. just highlight row 55 and right click delete it.
or highlight row and [ctrl]+[minus on numpad]
I tried to generate more normal results for the 250, but then found that the goal does not get met after a good few runs. So may have to change tack.
The macro will just run through the permiatations that the sheet generates so the less likely the outcome, the longer it will take to stumble upon it.
And yes, misscount by me, 51 rows.. just highlight row 55 and right click delete it.
or highlight row and [ctrl]+[minus on numpad]
I tried to generate more normal results for the 250, but then found that the goal does not get met after a good few runs. So may have to change tack.
ASKER
Yeah, found the template after my last post lol. Have amended, removed 51st number, put histogram on template and method required to calculate this. Any ideas how to best get a normal distribution result? Even if it means a small sacrifice somewhere, maybe on the average being within a percentage of the required rather than exact?
Goal-Seek3.xlsm
Goal-Seek3.xlsm
ASKER
The Bottom & Top values can deviate by upto 50% if required, so even if 8 - 20 is defined, the sheet will produce between 4 - 30.
The average can deviate by .5 so setting 14.5 may give 14 or 15 as overall average.
The percentage difference between OP's can be upto 20% either way.
This should allow more flexibility in calculating values that fall in normal distribution?
x amount of values in 0.1%, 2.1%, 13.6% and 34.1% for lower and same again for upper.
The average can deviate by .5 so setting 14.5 may give 14 or 15 as overall average.
The percentage difference between OP's can be upto 20% either way.
This should allow more flexibility in calculating values that fall in normal distribution?
x amount of values in 0.1%, 2.1%, 13.6% and 34.1% for lower and same again for upper.
Edit: Apologies folks, a failed attempt removed. (It didn't cope with averages far from the "real" one.)
ASKER
Thanx Brian, but unfortunately it does not produce normal distribution graph, I have added to file.
Random-V2.xlsm
Random-V2.xlsm
OK, to get a normal distribution you must AVERAGE the random values.
This will give a normal distribution as per the Central Limit Theorem.
See attached.
This will give a normal distribution as per the Central Limit Theorem.
See attached.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Barman,
With a few adjustments myself I now have it doing exactly as required based on your code and setup, thank you so much.
Brian, thank you for your attempt, I have used the VBA you used to create the random numbers within Barmans sheet, and will be using some other useful bits from it.
Regards
Wayne
With a few adjustments myself I now have it doing exactly as required based on your code and setup, thank you so much.
Brian, thank you for your attempt, I have used the VBA you used to create the random numbers within Barmans sheet, and will be using some other useful bits from it.
Regards
Wayne
ASKER
Cheers guys
On a side note:
A thanks from me to Brian, the use of FREQUENCY was new to me, this will be of real use to me and my coleagues. Is always nice to learn something new.
Thank you.
A thanks from me to Brian, the use of FREQUENCY was new to me, this will be of real use to me and my coleagues. Is always nice to learn something new.
Thank you.
Thanks, The_Barman, but I was just copying one of the earlier posts and only included it because I thought that the OP was more comfortable with it than some more obvious methods!
Plotting the 250 random generated points will not form a normal distiribution.
It will just be a random series of data points with no discernable pattern (hence random)
Now, the average of the 5 records across a single row plotted on a histogram will form a normal distribution as the average of values always tend towards normality.
So would you like the histogram to just plot the average of the 5 readings? (50 rows)