» Selecting a Set of Random Numbers, Avoiding Duplicates
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Problem: Selecting a set of 5 random numbers between 1 and 10, without getting duplicates.
Solution:
In Data Range (A1:C11):
Step 1:
Enter RAND function in column A:
=RAND()
Step 2:
Enter the following RANK formula in column B:
=RANK(A2,$A$2:$A$11)
Step 3:
Enter the numbers 1-10 in column C.
In Lottery Range (A14:B19):
In column B, use the VLOOKUP and ROW functions in the following formula:
=VLOOKUP((ROW()-ROW($A$19)+1),$B$2:$C$12,2,FALSE)
As a result, 5 unique random numbers between 1 and 10 will be displayed in column B.
The numbers will change with each recalculation (press the
Book Store:
Recommended Books:
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- Seven Habits Of Highly Effective People
- Windows XP Pocket Reference
- Microsoft PowerPoint Version 2002 Step by Step
- Adventure Capitalist: The Ultimate Road Trip
- The Intelligent Investor: The Classic Bestseller on Value Investing
No comments have been submitted.
