OmniDocHow to use formula to generate random numbers from 0 to 100 in Excel
Explain the function formula for generating random numbers between 0 and 100 in Excel, including the difference between RANDBETWEEN and INT plus RAND, and how to keep the random numbers fixed or refresh manually.
Key points of this article
- Integers from 0 to 100 can be directly used =RANDBETWEEN(0,100), and 0 and 100 at both ends may appear.
- The RAND function returns a decimal between 0 and 1, just multiply the range of values to get the desired range.
- RAND and RANDBETWEEN will change every time the table is recalculated. If you need to fix the results, you should copy and paste them as numerical values.
- The occurrence probability of 0 and 100 obtained using =ROUND(RAND()*100,0) is only half that of other integers, so it should not be used in sampling.
When doing lottery lists, random groups, simulated data or practice questions, many people need to quickly generate a batch of random numbers between 0 and 100 in Excel. It is obviously unrealistic to enter each word manually. There are various writing methods found on the Internet. Some use ROUND and some use INT. As a result, some numbers appear significantly less. This article is aimed at ordinary users who need to generate random numbers in Excel. It explains how to write commonly used formulas, what are their differences, and how to fix the results or refresh them.
After reading, you can directly enter a formula into the cell to get a random number from 0 to 100 in integer or decimal form, and know under what circumstances you need to convert the formula result into a fixed value to avoid changing the numbers as soon as the table is changed.
How to use formula to generate random numbers from 0 to 100 in Excel? One sentence conclusion
To generate an integer between 0 and 100, use the RANDBETWEEN function directly, written as =RANDBETWEEN(0,100); if a decimal is required, use the RAND function to multiply by 100. Which writing method you choose depends on whether you want integers or decimals, and whether the result needs to be fixed.
| your situation | Recommended practices | reason |
|---|---|---|
| Requires an integer between 0 and 100, either end is possible | =RANDBETWEEN(0,100) | The function directly specifies small and large values, the results are uniform, and the writing method is simple |
| There is no RANDBETWEEN in old versions of Excel, or you want to understand the underlying logic | =INT(RAND()*101) | RAND gets a decimal between 0 and 1, multiplies it by 101 and rounds to get an integer between 0 and 100 |
| Need random numbers with decimals | =RAND()*100 | The result is between 0 and 100, decimals are retained and no rounding is performed. |
| The results need to remain unchanged for archiving or verification | Generate first, then copy and paste with special emphasis as a value | The formula will change as the table is recalculated, and the result will be fixed after pasting it as a numerical value. |
| Want to use =ROUND(RAND()*100,0) to round | Not recommended for sampling | 0 and 100 each have only about half a chance to be obtained, and the distribution is uneven. |
Before you start: Confirm these points first
Before you start entering the formula, there are a few things that can affect whether the results are what you expect.
- Confirm whether you need an integer or a decimal. Lottery numbers, groups, and test scores generally require integers, so use RANDBETWEEN to save trouble; scenarios such as simulating measurement data may require decimals, so use RAND multiplied by 100.
- Confirm whether 0 and 100 are included. The result of RANDBETWEEN(0,100) contains these two endpoints. If you only want 1 to 100, you should change it to =RANDBETWEEN(1,100) instead of adding or subtracting the result.
- Confirm Excel version. RANDBETWEEN can be used directly in newer versions. If your version is older, or the formula prompts that the function cannot be found, you can use INT plus RAND instead.
- Confirm if fixed results are required. RAND and RANDBETWEEN are volatile functions and will be revalued whenever any calculation occurs in the table. If the result will be archived, sent to others for verification, or you do not want it to change by itself, it must be converted into a numerical value after generation.
- Confirm formula input environment. The function name, parentheses, and commas must be entered using English half-width symbols. If you enter a comma in the Chinese input method, the formula will be wrong.
Steps to enter a random number formula in Excel
The following is an example of generating a list of 20 integers between 0 and 100.
- Open the Excel worksheet and click on the cell where you want to place the random number, such as A1.
- Enter =RANDBETWEEN(0,100) in the edit bar or cell and press Enter to confirm. A1 will display an integer between 0 and 100.
- Move the mouse to the fill handle in the lower right corner of A1, drag it down to A20, and all cells will generate independent random numbers.
- If decimals are needed, you can enter =RAND()*100 in another column, fill downwards, and the result will retain decimal places.
- When you need to change the numerical range, just modify the two numbers in the brackets. For example, =RANDBETWEEN(1,50) generates an integer between 1 and 50.
- If you want to refresh all random numbers, press F9 and all volatile formulas will be recalculated.
- If you want the results to be fixed, select the entire column and copy it, then use Excel's Paste Special feature in its place, keeping only the numerical values. In this way, the current number is saved in the cell and will no longer change with the table.
- Finally save the file. If the number changes when you open it again after saving, it means that step 7 has not been completed and needs to be converted into a numerical value again.
Check each item after completion
After generating random numbers, it is recommended to take a minute to check the following items, especially if the results are going to be used in a formal setting.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Whether the value range is correct | Use the MIN and MAX functions to count this column. The small value should be no less than 0 and the large value should be no more than 100. | Check whether the two numbers in parentheses are written upside down or have more spaces, and refill them after modification. |
| Whether the result is an integer | Observe whether the cell has a decimal point, or use INT to judge | If you misuse RAND multiplied by 100, use RANDBETWEEN instead, or check the package with ROUND |
| Will the result change automatically? | Randomly modify an unrelated cell to see if the random number changes. | The description is still a formula. Follow the steps above to convert it to a numerical value. |
| Frequency of occurrence of 0 and 100 | After generating a large amount of data, count the number of occurrences of 0 and 100, which is roughly close to other numbers. | If it is obviously less, check whether ROUND (RAND () * 100jin0) is used, and use RANDBETWEEN instead. |
| Is there a duplicate value? | Use COUNTIF to count the number of times a number appears | RANDBETWEEN allows repetition. If not, see FAQ below. |
It is important to note that the random numbers generated by Excel are pseudo-random numbers, which are suitable for daily sampling, grouping and exercises, and are not suitable for situations with high security requirements, such as generating passwords or keys.
# which method is suitable for you?
If you just want to get a batch of integers quickly, you can use RANDBETWEEN directly, with almost no additional settings. If what you want to do is a simulation experiment, which requires a large number of decimals and statistics later, it is more appropriate to multiply RAND by 100. remember to make sure that the results are fixed before statistics. If you want to give the result to others for verification, or keep it as an official record, convert it to a numerical value after generation, and indicate the date of generation in the file for easy traceability.
# frequently asked questions (FAQ)
1. Will the numbers generated by RANDBETWEEN (0100) repeat?
Yes. Each time RANDBETWEEN takes a value independently, the same number may appear in multiple cells. If you need not to repeat, you can use RAND to generate auxiliary numbers in the next column, and then sort the two columns as a whole by the auxiliary column, and the first few rows are random results that do not repeat.
2. Why do I enter the formula and display decimals instead of integers?
This is usually because the cell format is set to regular or numeric and decimal places are retained. RAND itself returns decimals, and if you need an integer, you should use RANDBETWEEN or wrap the INT function around it.
3. How to generate only a random number and not want to refresh each time?
After entering the formula in the cell, copy it directly, and then paste it as a numerical value selectively, you can get a fixed number. After that, no matter how it is calculated, it will not change again.
4. The calculation of the formula is very slow. Is it a question of random numbers?
If there are a large number of RAND or RANDBETWEEN formulas in the workbook, the recalculation is triggered with each edit, and the table slows down. At this point, the results that no longer need to change can be converted into numerical values to reduce the number of volatile formulas.
# Summary
Generate random integers from 0 to 100 in Excel, written directly as = RANDBETWEEN (0100); if you need a decimal, use = RAND () * 100. Whichever is used, as long as the result needs to be fixed, it should be copied and selectively pasted as a numeric value, and the range and distribution should be checked before use, so that the generated random number is reliable.
