Menu
This article describes the formula syntax and usage of the RAND function in Microsoft Excel.
To generate random dates between two dates, you can use the RANDBETWEEN function, together with the DATE function. In the example shown, the formula in B5 is: = RANDBETWEEN ( DATE ( 2016, 1, 1 ), DATE ( 2016, 12, 31 )) This formula is then.
Description
RAND returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.
Note: As of Excel 2010, Excel uses the Mersenne Twister algorithm (MT19937) to generate random numbers.
Syntax
RAND()
The RAND function syntax has no arguments.
Remarks
- To generate a random real number between a and b, use:
=RAND()*(b-a)+a
- If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number. The formula will calculate and leave you with just a value.
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. You can adjust the column widths to see all the data, if needed.
Formula | Description | Result |
---|---|---|
=RAND() | A random number greater than or equal to 0 and less than 1 | varies |
=RAND()*100 | A random number greater than or equal to 0 and less than 100 | varies |
=INT(RAND()*100) | A random whole number greater than or equal to 0 and less than 100 | varies |
Note: When a worksheet is recalculated by entering a formula or data in a different cell, or by manually recalculating (press F9), a new random number is generated for any formula that uses the RAND function. |
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.
See Also
What Is a Random Variable?
A random variable is a variable whose value is unknown or a function that assigns values to each of an experiment's outcomes. Random variables are often designated by letters and can be classified as discrete, which are variables that have specific values, or continuous, which are variables that can have any values within a continuous range.
Random variables are often used in econometric or regression analysis to determine statistical relationships among one another.
Explaining Random Variables
In probability and statistics, random variables are used to quantify outcomes of a random occurrence, and therefore, can take on many values. Random variables are required to be measurable and are typically real numbers. For example, the letter X may be designated to represent the sum of the resulting numbers after three dice are rolled. In this case, X could be 3 (1 + 1+ 1), 18 (6 + 6 + 6), or somewhere between 3 and 18, since the highest number of a die is 6 and the lowest number is 1.
A random variable is different from an algebraic variable. The variable in an algebraic equation is an unknown value that can be calculated. The equation 10 + x = 13 shows that we can calculate the specific value for x which is 3. On the other hand, a random variable has a set of values, and any of those values could be the resulting outcome as seen in the example of the dice above.
In the corporate world, random variables can be assigned to properties such as the average price of an asset over a given time period, the return on investment after a specified number of years, the estimated turnover rate at a company within the following six months, etc. Risk analysts assign random variables to risk models when they want to estimate the probability of an adverse event occurring. These variables are presented using tools such as scenario and sensitivity analysis tables which risk managers use to make decisions concerning risk mitigation.
Types of Random Variables
A random variable can be either discrete or continuous. Discrete random variables take on a countable number of distinct values. Consider an experiment where a coin is tossed three times. If X represents the number of times that the coin comes up heads, then X is a discrete random variable that can only have the values 0, 1, 2, 3 (from no heads in three successive coin tosses to all heads). No other value is possible for X.
Continuous random variables can represent any value within a specified range or interval and can take on an infinite number of possible values. An example of a continuous random variable would be an experiment that involves measuring the amount of rainfall in a city over a year, or the average height of a random group of 25 people.
Drawing on the latter, if Y represents the random variable for the average height of a random group of 25 people, you will find that the resulting outcome is a continuous figure since height may be 5 ft or 5.01 ft or 5.0001 ft. Clearly, there is an infinite number of possible values for height.
A random variable has a probability distribution that represents the likelihood that any of the possible values would occur. Let’s say that the random variable, Z, is the number on the top face of a die when it is rolled once. The possible values for Z will thus be 1, 2, 3, 4, 5, and 6. The probability of each of these values is 1/6 as they are all equally likely to be the value of Z.
For instance, the probability of getting a 3, or P (Z=3), when a die is thrown is 1/6, and so is the probability of having a 4 or a 2 or any other number on all six faces of a die. Note that the sum of all probabilities is 1.
Key Takeaways
- A random variable is a variable whose value is unknown or a function that assigns values to each of an experiment's outcomes.
- Random variables appear in all sorts of econometric and financial analyses.
- A random variable can be either discrete or continuous in type.
Real World Example of a Random Variable
A typical example of a random variable is the outcome of a coin toss. Consider a probability distribution in which the outcomes of a random event are not equally likely to happen. If random variable, Y, is the number of heads we get from tossing two coins, then Y could be 0, 1, or 2. This means that we could have no heads, one head or both heads on a two-coin toss.
However, the two coins land in four different ways: TT, HT, TH, HH. Therefore, the P(Y=0) = 1/4 since we have one chance of getting no heads (i.e., two tails [TT] when the coins are tossed). Similarly, the probability of getting two heads (HH) is also 1/4. Notice that getting one head has a likelihood of occurring twice: in HT and TH. In this case, P (Y=1) = 2/4 = 1/2.