Create Random Numbers in Excel and Google SpreadSheets

Random Numbers in Excel Formula, VBA & SpreadSheets

Is it any Guessing Game? Yes. If you can predict the next number these functions can generate then, Microsoft has to work on upgrading its logic behind Generating Random Numbers.

Where do we generally use this kind of Logic for producing unpredictable numbers? Probable a Gaming station, Gambling, Lucky Draw Lottery System, Password Generation or Cracking, ID Numbers etc., In your case it might a different scenario.

The good news is that MS Excel provides easy to use Functions or formula to Generate Random numbers. All you have to do is to learn the syntax explained below.

Excel Formula for Random Number

The user of Excel might be using simple Worksheet Formula or they might be a VBA programmer. Microsoft provides features to product in simple predefined formula that can be used inside a Worksheet Cell and Even inside the VB Editor.

The below listed are the two formulas that can be used in a worksheet to create a Random Number. With these easy functions, you just need to learn how to restrict the number of digits you need the output to be.

Rand(): For Example =Round(Rand()*100,0) will generate a random number between 0 to 100 (inclusive).

General Syntax: (Rand() * (Higher_Num – Lower_Num + 1) )  + Lower_Num

RandBetween(bottom,top): For Example =RandBetween(1,50) will generate a unpredictable(mostly) number between 1 to 50.

Excel VBA Code to get Random Number

Since we have seen how to create a Random Number inside a Worksheet, let’s see how to do it with a Macro. Inside VBA Macro, you have to use a special function to ensure that Excel initializes the Random Generator system variables properly. This function name is Randomize().

Let see how to use this function with an Example Code snippet.

Option Explicit
'Visit OfficeTricks.com For More Free Codes & Tutorials
Public Sub Generate_RND_Number()
    Dim iRow As Integer
    For iRow = 1 To 100
        'Initialize the Seed
        VBA.Randomize
        
        'Generate a Random number within 1 to 100 and Write it in Worksheet
        ThisWorkbook.Sheets(1).Cells(iRow, 1) = VBA.Round(VBA.Rnd(1) * 100, 0)
    Next iRow
End Sub

To test this code, create a new Excel Workbook, Press Alt+ F11, Copy paste the above code in the VB Editor. Now coding is completed. To execute the code, press F5 and now view the First sheet in the Workbook. It will have 100 random numbers generated in first column.

Random Number in Google Drive SpreadSheets

It is amazing to know that there is no difference in this function name or Syntax in Google Spreadsheets as well. It is the same RAND & RANDBETWEEN in here as well. So, Just go to your Google Drive, Create a new Spreadsheet, and put the same functions in any cells and press Enter. Save and Refresh page to get different Random Numbers everytime.

Now, you have learned to generate non-sequential less predictable Random Number Generation Technique. Incorporate this in your Lottery system and engage people guessing the next number.