Introduction

This article describes how to randomly select 6 unique numbers from 1 to 49.

In a 6-from-49 lotto, a player chooses six numbers from 1 to 49 (no duplicates are allowed). If all six numbers on the player's ticket match those produced in the official drawing, then the player is a jackpot winner. This is true regardless of the order in which the numbers are drawn.
Source: Wikipedia

You learn how to:

  • Create an array of 49 numbers
  • Create random numbers
  • Construct a criterion for unique numbers
  • Create 6 unique numbers

Create an array of 49 numbers

In cell A2 typ:

=ROW($1:$49) + CTRL + SHIFT + ENTER

Select cell range A2:A51. Click formula bar. Press CTRL + SHIFT + ENTER

Create random numbers

Of course, you can use this formula to create random numbers between 1 to 49:

=RANDBETWEEN(1,49) + ENTER.

To be able to avoid duplicates we can use Large function and a criterion. Simplified formula: =Large(array_numbers, Randbetween(1,49))

In cell B2:

=LARGE(ROW($1:$49), RANDBETWEEN(1, 49))  + CTRL + SHIFT + ENTER

Select cell range B2:B51. Click formula bar. Press CTRL + SHIFT + ENTER.


Learn more about RANDBETWEEN function:

Excel RANDBETWEEN and RAND functions – How to generate random numbers and text

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]

Comments(10) Filed in category: Excel

Construct a criterion for unique numbers

How do we remove numbers already selected, avoiding duplicates?

In our first cell C2, the formula can select any number from the number array. But in our second cell, we must remove the number in cell C2 from the number array to avoid duplicates.

ROW($1:$49) multplied by a criterion.

Adding a criterion to the simplified formula:  =Large((array_numbers)*Criterion1, Randbetween(1;49))

The criterion in cell C2:

COUNTIF($C$1:C1;ROW($1:$49)) creates this array:

{0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}.

Cell C1 is empty and is not equal to any number in the array ROW($1:$49).

COUNTIF function

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel

What happens in cell C3:

COUNTIF($C$1:C2;ROW($1:$49)) creates this array:

{0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}.

Cell C2 contains the number 22 and is equal to array value 22 in ROW($1:$49).

Randbetween function returns a random number between the numbers you specify. In this example the numbers are 1 and 49.

The question is how do we calculate the number we need to specify?

(COUNTIF($C$1:C1, ROW($1:$49))=0 returns how many numbers left there are to select randomly.

{TRUE,  TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE}

Excel can´t sum that array so using double negations converts the array.

SUM(--(COUNTIF($B$1:B1, ROW($1:$49))=0))

SUM({ 1,  1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1}) equals 49.

Excel SUM function

The SUM function in excel allows you to add values, the function returns the sum in the cell it is […]

Comments(0) Filed in category: Excel, SUM function

In cell C2:

=LARGE((COUNTIF($C$1:C1, ROW($1:$49))=0)*ROW($1:$49), RANDBETWEEN(1, SUM(--(COUNTIF($C$1:C1, ROW($1:$49))=0)))) + CTRL + SHIFT + ENTER

Copy cell C2. Paste it down to cell C51.


Create 6 unique numbers

Array formula in D2:

=IF(ROW(1:1)<7, LARGE((COUNTIF($D$1:D1, ROW($1:$49))=0)*ROW($1:$49), RANDBETWEEN(1, SUM(--(COUNTIF($D$1:D1, ROW($1:$49))=0)))), "")

Make sure you enter this formula as an array formula.
Copy cell D1 and paste down to cell D7.

Press F9 to refresh list.

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel

Download excel sample file for this tutorial.
Lottery-numbers2.xls
(Excel 97-2003 Workbook *.xls)