Random Number Generator

[FRED_SHEET]

I am trying to use a Random Number Generator to test a worksheet I am developing. I have been using the expression shown below with success to generate a number between 1 and 63 to put in a cell.

=TRUNC(RAND()*(64-1)+1)

However, I would like to test this cell with a random number between 0 and 63 and a null entry or blank in the cell generated randomly. I tried using the expression below but it doesn't seem to give be a zero, maybe I have tried enough times.

=TRUNC(RAND()*(64-0)+1)

Also, is there anyway to generate a blank entry randomly along with random entries from 0 to 63?

<colgroup><col></colgroup><tbody>
</tbody>

 

[SteveO59L]

have you considered randbetween(0,63) ?

 

[NARAYANK991]

Hi ,

The RAND() function generates a random number between 0 and 1 , 0 inclusive , 1 exclusive.

When you have 0 being generated , multiplying the result by any number and then adding 1 will always give you 1 , never 0.

To generate a random number between 0 and 63 , just use :

=RANDBETWEEN(0,63)

Now , to generate a blank randomly , try using :

=RANDBETWEEN(0,64)

in one column , and in a second column , use a formula such as :

=IF(random number < 64 , random number , "")

So , as long as the random number generated is between 0 and 63 , the same will be reflected in the second column , but when the random number generated is 64 , the second column will contain a blank.

 

[mikerickson]

Something like

=IF(RAND()<.5, "", RANDBETWEEN(1,63))

Adjust the .5 to taste.

 

[wideboydixon]

Cheekily chiming in late in the day

You could try:

=IFERROR(1/(1/RANDBETWEEN(0,64))-1,"")

WBD

 

[FRED_SHEET]

Hi ,

The RAND() function generates a random number between 0 and 1 , 0 inclusive , 1 exclusive.

When you have 0 being generated , multiplying the result by any number and then adding 1 will always give you 1 , never 0.

To generate a random number between 0 and 63 , just use :

=RANDBETWEEN(0,63)

Now , to generate a blank randomly , try using :

=RANDBETWEEN(0,64)

in one column , and in a second column , use a formula such as :

=IF(random number < 64 , random number , "")

So , as long as the random number generated is between 0 and 63 , the same will be reflected in the second column , but when the random number generated is 64 , the second column will contain a blank.

THANK YOU - using this I was able to generate a cell with the random numbers I wanted and a blank.

 

[Rick Rothstein]

THANK YOU - using this I was able to generate a cell with the random numbers I wanted and a blank.

The formula wideboydixon posted in Message #5 will do what you want without needing to use two columns.

And here is another way to do it without using two columns...

=SUBSTITUTE(RANDBETWEEN(0,64),64,"")

 

[Rick Rothstein]

The formula wideboydixon posted in Message #5 will do what you want without needing to use two columns.

And here is another way to do it without using two columns...

=SUBSTITUTE(RANDBETWEEN(0,64),64,"")

Actually, in rereading your original post, I think you wanted your random numbers to go from 1 to 63 (plus a blank), not 0 to 63 (plus a blank) like the formula above does. If that is correct, then use this formula instead...

=SUBSTITUTE(RANDBETWEEN(0,63),0,"")

By the way, mikerickson has an interesting formula in Message #4 ... it allows you to set an approximate percentage of the time the blank should appear. As written, Mike's formula generates a blank about half of the time and the other half of the time a number between 1 and 63. If you wanted the blank to appear about one quarter of the time, you would change the 0.5 to 0.25. If you wanted the blank to appear about 1/64th of the time (making it a fairly generated random number), you would use either 1/64 or 0.015625 in place of the 0.5 he used.

 

[mikerickson]

Rick the last formula will never return a result divisible by 10. The 0 gets blanked.

SUBSTITUTE(RANDBETWEEN(1, 64),64,"") will avoid that issue

 

[Rick Rothstein]

Rick the last formula will never return a result divisible by 10. The 0 gets blanked.

SUBSTITUTE(RANDBETWEEN(1, 64),64,"") will avoid that issue

Yes, you are correct... of course.

I had tried to get cute and save a couple of characters without thinking about the consequences. Thanks for catching that.