Rand
- Thread starter bobhc
- Start date
SirJB7
Excel Rōnin
Hi, b(ut)ob(ut)c!
Ok, let me think. Until now, just VBA comes to my mind. I'll come back this afternoon, 3PM GMT-3. See you later!
Regards
EDIT: Now it's 4AM, and that's why I'm getting sleepy...
There's a problem with RAND function (and RANDBETWEEN too), they are volatile functions, that's to say, they change their values each time you recalculate the worksheet and at open time too... so first problem arises: how do you maintain the random numbers generated? The answer is or manually copying or assigning them from VBA code
That leds us to change our first assumption: keep it with formulas is still needed?
Ok, let me think. Until now, just VBA comes to my mind. I'll come back this afternoon, 3PM GMT-3. See you later!
Regards
EDIT: Now it's 4AM, and that's why I'm getting sleepy...
There's a problem with RAND function (and RANDBETWEEN too), they are volatile functions, that's to say, they change their values each time you recalculate the worksheet and at open time too... so first problem arises: how do you maintain the random numbers generated? The answer is or manually copying or assigning them from VBA code
That leds us to change our first assumption: keep it with formulas is still needed?
NARAYANK991
Excel Ninja
Hi Bob ,
The main point is how many random numbers do you want to generate ?
If it is only a few , then the simplest way is to generate random numbers between a much larger between-set , say between 1000 and 1000000 , and then select only a few numbers from these randomly generated numbers ; the chances are bright that all your numbers will be unique.
If you are going to generate 70 or more numbers between 15 and 100 ( whose difference is only 86 ) , then randomness does not mean much , since almost all the numbers will be present , only in random order.
Try this out :
1. In any column , say column D , fill in the numbers from 15 to 100 ; they will be in sequence.
2. In the next column , which will be column E , enter the formula =RANDBETWEEN(15,100) , in the 86 cells corresponding to the cells which have numbers in column D. So if your original filled-in numbers are from D7 to D92 , the formulae should be between cells E7 and E92.
3. Sort both the columns on column E ; your numbers in column D are in random order.
Narayan
The main point is how many random numbers do you want to generate ?
If it is only a few , then the simplest way is to generate random numbers between a much larger between-set , say between 1000 and 1000000 , and then select only a few numbers from these randomly generated numbers ; the chances are bright that all your numbers will be unique.
If you are going to generate 70 or more numbers between 15 and 100 ( whose difference is only 86 ) , then randomness does not mean much , since almost all the numbers will be present , only in random order.
Try this out :
1. In any column , say column D , fill in the numbers from 15 to 100 ; they will be in sequence.
2. In the next column , which will be column E , enter the formula =RANDBETWEEN(15,100) , in the 86 cells corresponding to the cells which have numbers in column D. So if your original filled-in numbers are from D7 to D92 , the formulae should be between cells E7 and E92.
3. Sort both the columns on column E ; your numbers in column D are in random order.
Narayan
bobhc
Excel Ninja
Good day SirJB7 and NARAYAANK991
I did a randbetween(10,100)in coloum A1:A30 and got these results
34
15
83
61
19
44
36
66
36
58
58
53
23
46
16
20
88
19
21
67
38
27
56
66
76
18
51
81
89
73
As you can see there are six numbers repeated,I was just trying to get unique numbers,I was then going to copy the values to another column so that these numbers would be kept in the spreadsheet
I did a randbetween(10,100)in coloum A1:A30 and got these results
34
15
83
61
19
44
36
66
36
58
58
53
23
46
16
20
88
19
21
67
38
27
56
66
76
18
51
81
89
73
As you can see there are six numbers repeated,I was just trying to get unique numbers,I was then going to copy the values to another column so that these numbers would be kept in the spreadsheet
Chandrashekar.A
New Member
=rand()*(100-15)+15
formula will generate a number between 15 and 100.
formula will generate a number between 15 and 100.
SirJB7
Excel Rōnin
Hi, Chandrashekar.A!
You posted on a bit old thread, usually you shouldn't expect that the OP would come back and read it, and in this specific case b(ut)ob(ut)hc has become a Ninja long time ago.
Regards!
You posted on a bit old thread, usually you shouldn't expect that the OP would come back and read it, and in this specific case b(ut)ob(ut)hc has become a Ninja long time ago.
Regards!
bobhc
Excel Ninja
Good evening SirJB7, I hope life is good for you
I must admit I was a little surprised to see this post re-emerge, but I assumed the poster was just searching and then posted another example............... But all welcome....... Difficult to read until the cobwebs had been removed....
.
I must admit I was a little surprised to see this post re-emerge, but I assumed the poster was just searching and then posted another example............... But all welcome....... Difficult to read until the cobwebs had been removed....
.
