Random number generation with RAND function in Excel

One way to generate random numbers in Excel is to use the function: EDGE . on my own EDGE generates a limited range of random numbers, but by using it in formulas with other functions, you can expand the range of values ​​so that:

  • EDGE you can make it return random numbers in a certain range, such as 1 and 10 or 1 and 100, by specifying the top and bottom values ​​of the range,
  • You can reduce the output of a function to integers by concatenating it with a function TRUNC A that truncates or removes all decimal places from a number.

Function EDGE returns a uniformly distributed number greater than or equal to 0 and less than 1. While the normal description for the range of values ​​generated by a function is between 0 and 1, the range is actually, more precisely, between 0 and 0.999.. .

These instructions apply to Excel 2019, 2016, 2013, 2010, Excel Online, and Excel for Office 365.

Content
  1. Syntax and arguments of the RAND function
  2. Number generation with RAND
  3. Generate numbers in a range
  4. Generate random integers with RAND
  5. RAND function and volatility

Syntax and arguments of the RAND function

Function syntax refers to the layout of the function and includes the function name, parentheses, comma separators, and arguments. Syntax for a function EDGE :

 = RAND () 

Unlike the function EDGE BETWEEN for which both high and low arguments are required, the RAND function takes no arguments.

You can see some sample features EDGE in the image above.

  • The first example (line 2) comes in the function EDGE on it’s own.

  • The second example (lines 3 and 4) creates a formula that generates a random number between 1 and 10 and between 1 and 100.

  • The third example (line 5) generates a random integer from 1 to 10 using the function TRUNC .

  • The last example (line 6) uses the function ROUND to reduce the number of decimal places for random numbers.

Number generation with RAND

Again, since the RAND function takes no arguments, you can enter them by clicking on a cell and typing = edge() which results in a random number between 0 and 1 in the cell.

Generate numbers in a range

The general form of the equation used to generate a random number within a given range is:

 = RAND () * (High-Low) + низкая 

High and short indicate the upper and lower limits of the desired number range. For example, to generate a random number between 1 and 10, enter the following formula into a worksheet cell:

 = RAND () * (10-1) + 1 

Generate random integers with RAND

To return an integer — an integer with no decimal part — the general form of the equation is:

 = ОТБР (RAND () * (High-Low) + низкая) 

Instead of removing all decimals with the function TRUNC we can use the following function: ROUND in combination with EDGE to decrease the number of decimal places. in any number up to two.

 = ROUND (RAND () * (High-Low) + Low, десятичные) 

RAND function and volatility

Function EDGE is one of the volatile Excel functions; it means that:

  • The function recalculates and generates a new random number every time someone makes changes to the sheet, including things like adding new data.

  • Any formula that directly or indirectly depends on the cell containing the volatile function is also recalculated every time someone makes changes to the spreadsheet.

  • Use caution when using volatile functions in worksheets or workbooks with large amounts of data, as they can slow the program’s response time due to the frequency of recalculations.

You can also force the RAND function to generate new random numbers without making any other changes to the table by pressing the key. to push F9 on keyboard. This action will cause the entire worksheet to be recalculated, including all cells containing the RAND function.

You can also use the key F9 to avoid accidentally changing the number every time someone changes the sheet:

  1. Click on the sheet cell where you want the random number.

  2. Enter a function = edge() in the formula bar above the worksheet.

  3. Press key F9 to change the function: EDGE to a static random number.

  4. Press key Enter on the keyboard to display a random number in the selected cell.

  5. Now press F9 does not affect the random number.

Leave a Reply

Your email address will not be published.