Problem: I want Excel to generate numbers for the lottery. Once a number is chosen, I don't want that number to appear again. Using RANDBETWEEN, it is possible to get duplicates.
- Eventually, RANDBETWEEN returns duplicates.
Strategy: to solve this problem, you need to sort the 56 numbers into a sequence and choose the top five numbers from the list. This will prevent any duplicates from showing up.
Say that you want to generate five numbers from 1 to 56. Follow these steps:
- Select a range that is one column wide by 56 rows tall.
- Type =RAND(). Press Ctrl+Enter to enter that formula in all of the cells. In my example, I used A1:A56.
From here, you want to find the largest values using =LARGE(A1:A56,1) then =LARGE(A1:A56,2), then LARGE(A1:A56,3), and so on. Once you locate the largest value, use MATCH to find that value within the list. The position in the list represents the lotto number.
- Combining all of those formulas together, you get =MATCH(LARGE($A$1:$A$56,COLUMN(A1)),$A$1:$A$56,0). Enter this formula in C2:G2.
- For the extra ball, use a regular old =RANDBETWEEN(1,46).
- You won't get any repeats in C2:G2.
Additional Details: For PowerBall, enter numbers in A1:A59. Change the 56 in the formula above to a 59. Change the formula in H2 to get numbers from 1 to 39.