How Much Does Laminating Cost?

Text Compare Strings without Repeats Generate Random Number in a Range

Download File

"I need to generate 10 random numbers between 1 and 20 without repeat none Is it possible without using of VBA? "

Let's see how to fix this problem without using macros. For this we will use two functions: RANDOM and RANK .

The RAND function returns a random number greater than or equal to 0 and less than 1, uniformly distributed. Each time you calculate the worksheet, it returns a new random number. No arguments so that the syntax is = RAND ()

RANK function can make inquiries at the post value hierarchy within a range or array .

1. We started building the next sheet:

2. In the range B4: B23 we generate 20 random numbers. We selected this range (B4: B23) and the selected range began to write the following formula:

= RAND () and press Ctrl + Enter . In this way one will fill the entire selected range attack (you can also write the same formula in B4, then copy down to cell B23). The result is shown in the figure below. You can check it by pressing F9 will recalculate all the random numbers.

3. Select again the range B4: B23 and click do in Name Box (left of formula bar) and write the name list, and press Enter .

4. We are located in cell E3 and type the formula:

= RANK (B4, list)

Thus excel "order" B4 the random value based on the order to occupy between 1 and 20 Why between 1 and 20? Because we generated 20 random numbers collected in the range name list a.

5. E3 copy the formula down to cell E12 and problem solved.

Try pressing the F9 key to generate new random numbers. Obviously you can hide column B if not interested to see the 20 random numbers that result.