Generating Test Data in VBA

Knubee · Mar 22, 2010

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5Cgbrader%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5Cgbrader%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5Cgbrader%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> [FONT=&quot]Hello,<o

></o

>[/FONT]
[FONT=&quot]<o

> </o

>[/FONT]
[FONT=&quot]I would like use excel to generate a few test data suites for a couple of applications. Once generated the data will be placed in an Access database using ADO (at least for starters). I think I have the ADO aspect covered (but any help would be welcome as well). <o

></o

>[/FONT]
[FONT=&quot]<o

> </o

>[/FONT]
[FONT=&quot]I really need help on generating the test data.<o

></o

>[/FONT]
[FONT=&quot]<o

> </o

>[/FONT]
[FONT=&quot]The first test suite looks like this. I need to generate 100 integers. These integers are in the range from 0 to 10. Each of these integers are selected from a normal distribution with mean = 7 and standard deviation = 1. Then I need another set of 100 with mean = 5 (for example) and std = 2 (for example). All told, I need 20 sets of 100 with perhaps some differences in mean and standard deviation.<o

></o

>[/FONT]
[FONT=&quot]<o

> </o

>[/FONT]
[FONT=&quot]How could I use excel to generate this data? Thanks in advance.<o

></o

>[/FONT]

AgentSmith · Mar 23, 2010

Try this:

Code:

Sub rander()
    Dim i As Integer
    
    ThisWorkbook.Activate
    Worksheets(1).Activate
    Randomize
    
    For i = 1 To 100
        Cells(i, 1).Value = WorksheetFunction.NormInv(Rnd(), 5, 3)
    Next
End Sub

... where mean = 5 and std dev = 3 - change as needed.

Knubee · Jun 11, 2010

Sorry it took so long to thank you, but thank you for your help.

Generating Test Data in VBA

Knubee

New Member

Excel Facts

AgentSmith

Well-known Member

Knubee

New Member

Similar threads

Forum statistics

Share this page

Generating Test Data in VBA

Knubee

New Member

Excel Facts

AgentSmith

Well-known Member

Knubee

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock