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.

Visual Basic

1

2

3

4

5

6

7

8

9

10

11

12

OptionExplicit

'Visit OfficeTricks.com For More Free Codes & Tutorials

PublicSubGenerate_RND_Number()

DimiRow AsInteger

ForiRow=1To100

'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)

NextiRow

EndSub

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.

Author: Kumarapush

Kumarapush has experience in IT industry for 12+ Years including 10 Years in MNC. Has vast expertise in Microsoft Excel automation using VBA Macro. Excel to CSV converter is one of his famous tool that has been downloaded & used by 68,000 users around the globe.