VideoPhoto

Showing posts with label random numbers. Show all posts
Showing posts with label random numbers. Show all posts

26 September, 2021

RAND() function: Distribution of the ratios of TWO Rand() functions

Excel Rand() function generates a random real number in a standard continuous  UNIFORM distribution of less than 1 and equal/greater than 0. It means that in this distribution every value between 0 and 1 is equally likely to be chosen.The mean of the distribution is 0.5. The variance is equal to 1/12 (=0.083333).

The Rand() function is used mainly as a random number generator. It's distribution is pretty simple and straightforward. Much more interesting are distributions of quotient (ratio), product, difference or sum of two independently run Rand() functions. Their usefulness in practical applications is not yet well defined.

I've looked closer at the distribution function of the quotient, denoted here as F(z), where z=X2/X1. The result of distribution, based on the sample of 2000 outcomes (vertical axis) is presented on the following chart (z on horizontal axis):


23 May, 2021

Pick your lucky Lotto numbers

If you play Lotto, how do you pick your lucky numbers?

Everyone has their own method. Some people like to analyze past draws and study the statistics to select potential winning patterns. Others rely on totally random ways. My own experience shows that statistics helps a little bit with winnings at the low end only. That’s it.

No method can guarantee a big win. Lotteries are completely random. To check out my 'independent' serendipity - years ago - I decided to create my own Lotto number picker coded with VBA. It’s one of my modest and oldest Excel creations. Here's its face in a worksheet:

Simple and functional. It just picks six lucky numbers for you to play in a lottery like Lotto649 or something close to it. It can be modified quite easily for other lottery games based on random selections. You can get your very own quick picks on your own computer.

23 April, 2021

Generate unique random alphanumeric characters and strings

If you play lottery games of lotto type or want to get a list of some unique random numbers, letters or expressions, Excel is very helpful in generating them. There are many ways to accomplish that. Here are just couple of them.

To create a list of unique random numbers using an array formula

First, format a sellected cell, e.g. A1, as Text. Enter a range of numbers from which to draw, in this format: 1-50 . Next, enter in cell A2 the following array formula:

=SMALL(IF(COUNTIF(C$1:C1,ROW($1:$50))<>1,ROW($1:$50)),1+INT(RAND()*(RIGHT(C$1,LEN(C$1)-FIND("-",C$1))-LEFT(C$1,FIND("-",C$1)-1)+1-ROW()+ROW(C$2))))

To enter it correctly, hold CTRL+SHIFT and press ENTER key on your keyboard. If you need to generate random unique numbers from a different range, you have to replace the numbers (1 and 50) in both A1 and A2 cells.

Now you need to copy cell A2 and paste it to as many rows as many random numbers you are expecting to obtain (up to 50 in the example).

To create a list of unique random numbers using UDF (user defined function)

  • Press ALT+F11 on your keyboard to open up the VB Editor.
  • If no Module is available within your Project, then right-click within the VBA Project Explorer and select Insert, then choose Module from the menu.
  • Enter the following code in the module:
Function GetUnique(Lo As Integer, Hi As Integer, Nr As Integer) As String
'Generates x unique random numbers between any 2 numbers you specify
'e.g. =GetUnique(1,50,8) produces 8 unique random numbers between 1 and 50

    Dim iArr As Variant
    Dim i As Integer
    Dim r As Integer
    Dim temp As Integer
    Application.Volatile
    ReDim iArr(Lo To Hi)
    For i = Lo To Hi
        iArr(i) = i
    Next i
    For i = Hi To Lo + 1 Step -1
        r = Int(Rnd() * (i - Lo + 1)) + Lo
        temp = iArr(r)
        iArr(r) = iArr(i)
        iArr(i) = temp
    Next i
    For i = Lo To Lo + Nr - 1
        If i = Lo Then GetUnique = GetUnique & "" & iArr(i)
        If i > 1 Then GetUnique = GetUnique & " * " & iArr(i)
    Next i
    GetUnique = Trim(GetUnique)
End Function

  • Use the function in any selected cell by entering it in the format shown above on the comment line.

To create a random letter of alphabet (eithe Upper- or Lowercase)

Use this formula:  
=IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122))) 

It generates a random letter from a to z and A to Z characters.

To create a string of random letters,

like in this example:

 
 
 
 

you need to use "&" character to add the letters together:

=IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))&IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))&IF(RAND()<=0.5,CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))

This specific formula generates a string of three random letters. If you drag the cell fill handle over to some range you want to cover with this formula, you will get the range filled with random 3-letter strings. The formula can be quite easily modified to get unique random strings including any and many (more than three) alphanumeric characters.