Solved

Creating a random number generator with a defined start/end range and standard deviation

Posted on 2013-01-25
12
187 Views
Last Modified: 2013-09-25
Hi,

I have a sheet that creates 5 cols by 50 rows (250 numbers) using randbetween of two defined values, usually between 5 and 30.  These individual rows are then averaged (5 numbers averaged to generate 1) so in total 50 numbers.  

These values are then used to create a normal distribution histogram.

However, I would like to define a number in row 1 of each of the 5 columns which will act as the standard deviation expected.  So once the figures are created and the average of the 5 cols is created, the overall standard deviation of the 50 figures is equal to that defined.

How could I do this?

My initial sheet attached
0
Comment
Question by:MirageSF
  • 5
  • 4
12 Comments
 

Author Comment

by:MirageSF
ID: 38821070
Sorry, fixed sample as attached, please use this one thx
Sample.xlsx
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38821397
MirageSFP,

Few questions, please...
(1) Is B1 the target? (The sheet says B10.)
(2) Did you mean the target to be 10? That's an enormous distance from anything that would be expected.
(3) "Close as possible" needs to be defined. +/- 10%, 1%, .1%?

Edit: What version of Excel?

Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38821420
MirageSFP,

Please see the attached. Press the blue button to run a macro that cycles through up to 100 random iterations looking for the first solution within 1% of the target. The code is...
Option Explicit

Sub Randomish()
Dim xMin    As Double
Dim xActual As Double
Dim xTarget As Double
Dim i       As Long

Sheets("Sheet2").Activate

xTarget = Range("B1")

xMin = 100000000

Application.ScreenUpdating = False
    
    For i = 1 To 100
    
        Range("B6:F105").Formula = "=(RANDBETWEEN($B$3,$B$4)+(RANDBETWEEN(0,9)/100))"
    
        Range("B6:F105").Copy
        Range("B6:F105").PasteSpecial xlPasteValues
        
        xActual = Range("H1")
        
        If Abs(xTarget - xActual) / xTarget < 0.01 Then
            MsgBox ("Run complete - difference is " & Format(Abs(xTarget - xActual) / xTarget, "0.00%"))
            Exit Sub
        End If
        
        If Abs(xTarget - xActual) < xMin Then xMin = Abs(xTarget - xActual)
        
    Next

MsgBox ("Sorry - couldn't get within reach. Closest was " & Format(xMin / xTarget, "0.00%") & ". Please try again.")

Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.Sample--4--V2.xlsm
0
 

Author Comment

by:MirageSF
ID: 38821627
Hi Brian,

Thanx for that, problem is obviously if Randbetween values are say 8-15 as they are then unlikely to get a 10 deviation which is expected, however even when I change the range from 8-30 and set the deviation to 10, it does not seem to get much higher than 3?

1) Yes sorry typo B1 is target.
2) Would not expect it to be bang on 10, but near as possible, expecting exactly 10 would cause massive delay in calculating.
3) I would say +/- 20% would be a exceptable range.
4) Excel 2010.

Thank you
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38821665
MirageSF,

expecting exactly 10 would cause massive delay in calculating.
Spot on - an SD of 10 isn't quite age of the universe stuff, but I think it'd comfortably see the pair of us dead and buried. (For normally distributed data, the odds of getting an SD of even 7 is 1/390,682,215,445.)

What is the table for?

Regards,
Brian.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:MirageSF
ID: 38821706
Brian, like the dead and buried before calculation lol.

The table will be used for simulation data, generally looking for a deviation for each set of around 1-3 points, but occassionaly will need this to be around 10 if defined, although rare and more likely to stay 5 or below.

Is it not possible to have the random values as they are created now, but occassionaly add a bump value of say 10 to force the overall average up and therefore bring the deviation closer to target?

Cheers
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38821742
MirageSF,

The attached will now generate a match between B1 and H1 of <= +/-1%. However, I think it would be fair to describe it as a statistical abomination.    :)

Please note that I've moved the Max and Min figures up a row to make room for the "Adjustment Factors" above each column.

Finally, you'd be doing yourself a favour if you deleted this question and opened a new one - this time including the Mathematics Topic (as well as Excel)!

Regards,
Brian.
Sample--4--V3.xlsm
0
 
LVL 24

Expert Comment

by:Steve
ID: 38826337
Isn't this a very similar question to this one:

http://rdsrc.us/9qtQNl
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38826383
The_Barman,

Yes, that was my first thought, but this one is a lot simpler (assuming that MirageSF is content with the above).

Regards,
Brian.
0
 

Author Comment

by:MirageSF
ID: 38826400
Hi Barman,

Yes you are correct, it was your mathematical genius which created that for me, for which I have been using for a while now.  However, that version allowed me to define the range i.e. 8-15 which was great, but no control really over individual Average & Standard Deviation values which I required later on.

The updated version I am using NORM.DIST to generate my random numbers which gives me good control over average and standard deviation, but zero control over the range!.  Unless I use MEDIAN in front of the NORM.DIST command as matthew Patrick pointed out, but this then does not create normal distribution.

So each column of 50 numbers needs range from range to, deviation and average expexted values, and then needs to generate 50 numbers with these factors which would be normally distributed.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now