Random number generation help needed urgently

[nitar]

Hi,


Im trying to generate random scores (from 45 to 95) for about 200 students for 10 courses. Each student is only allowed to do 5 out of the 10 courses.

Please any help with how to do this using excel vba?

Thanks in advance!

 

[Luke M]

Does it have to be VBA? The formula:

=RANDBETWEEN(45,95)

would work pretty nicely.

 

[Hui]

and if it absolutely has to be VBA use:

application.WorksheetFunction.RANDBETWEEN(45,95)

 

[nitar]

Thanks,

Yes I want it to be with VBA. I want the data to look like this when its done:


Student No Business Economics Engineering Geography Mathematics Physics Politics Sociology Biology Computer Science

1 51 61 37 51 61

2 71 67 71 83 67

3 68 57 71 57 71

4 72 65 52 72 65

5 86 64 63 83 83

6 75 46 79 75 46

7 92 63 62 92 62


(Sorry the data looks this way, I'm new here)

 

[Hui]

Assuming that the number 51 is in B2 under Business

why not just type

=Randbetween(45,95) press enter

And copy B2 across and down to fill the area

 

[Mike86]

Here's a page with VBA code for doing some statistical functions in Excel, including random numbers: http://www.anthony-vba.kefra.com/vba/excelvba-statistics.htm


Another interesting method is the Data Analysis Tool Kit Add-In. You will need the add-in installed, but it is standard with Excel and very useful. The tool kit has a random number generator that will handle multiple variables and different statistical distributions. Also, since this simply generates the numbers instead of imbedding the function, the numbers don't change all the time.

 

[nitar]

Thanks for your help people, But you don't seem to get my question.Please if you could paste the data into excel, you would understand better. Let me try to explain:

Row 1: there are scores under Business, Engineering, Economics, Physics, Sociology,

Row 2: there are scores under Engineering, Physics, Politics, Sociology, Computer Science

Row 3: there are scores under Business, Economics, Engineering, Mathematics, Politics

.

.

.

Row 200:........

I hope I have been able to explain it better.

Thanks.

 

[Luke M]

Ah, so we only want 5 of the 10...

Similar to what was above, but we'll have 10 helper columns, (say, AA:AJ)

Then, formula in helper columns is simeply:

=RAND()

B2 then is:

=IF(AA2>=LARGE($AA2:$AJ2,5),RANDBETWEEN(45,95),"")


If you want to stick with VB, you could build some arrays loaded with random numbers and do something similar.

 

[nitar]

Thanks Luke M, I'll try this out.