Hi everyone,
Since there are several threads on this board showing an interest in Lexicographic Index Numbers, better known as Combination Sequence Numbers (CSN's), I thought I would put together something that would calculate the vast majority of C(n, k) Lexicographic Index Numbers, where n is the total numbers drawn from, and k is the total numbers drawn.
I have created SIX Excel formulas to do this for 2, 3, 4, 5, 6 & 7 total numbers drawn (k numbers) in such a way that the formulas do NOT need to be amended or adjusted for the individual Lotto. Depending on which Lotto you play you just use the relevant formula below and copy it down as far as it is needed.
Setup the criteria for the k and n numbers.
We will use cell K2 for the total numbers drawn (k).
We will use cell K3 for the total numbers drawn from (n).
Setup the draws and formulas.
We will use cells B7:H7 and continuing down for the ACTUAL numbers drawn for each draw, so if you have 4 numbers drawn in your Lotto then cells B7:E7 and continuing down will only have numbers in them. If you have 6 numbers drawn in your Lotto then cells B7:G7 and continuing down will only have numbers in them etc.
In cell K7, COPY & PASTE the formula below that applies to your particular Lotto and copy it down as far as it is needed.
TWO NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-C7>0,COMBIN(K$3-C7,K$2-1),0)
THREE NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-D7>0,COMBIN(K$3-D7,K$2-2),0)
FOUR NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-E7>0,COMBIN(K$3-E7,K$2-3),0)
FIVE NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-(K$2-4)-E7>0,COMBIN(K$3-E7,K$2-3),0)-IF(K$3-F7>0,COMBIN(K$3-F7,K$2-4),0)
SIX NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-(K$2-4)-E7>0,COMBIN(K$3-E7,K$2-3),0)-IF(K$3-(K$2-5)-F7>0,COMBIN(K$3-F7,K$2-4),0)-IF(K$3-G7>0,COMBIN(K$3-G7,K$2-5),0)
SEVEN NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-(K$2-4)-E7>0,COMBIN(K$3-E7,K$2-3),0)-IF(K$3-(K$2-5)-F7>0,COMBIN(K$3-F7,K$2-4),0)-IF(K$3-(K$2-6)-G7>0,COMBIN(K$3-G7,K$2-5),0)-IF(K$3-H7>0,COMBIN(K$3-H7,K$2-6),0)
So just to summarise:
In cells B7:H7 and continuing down are the ACTUAL lotto numbers drawn.
In cell K2 is the total numbers drawn for your particular Lotto (k).
In cell K3 is the total numbers drawn from in your particular Lotto (n).
In cell K7 is the formula pertaining to your particular Lotto. Copy this formula down as far as it is needed.
You can format the cells with the Lexicographic Index Numbers to account for the thousands separator to make it easier to read.
That’s it!
Regards,
PAB
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
Since there are several threads on this board showing an interest in Lexicographic Index Numbers, better known as Combination Sequence Numbers (CSN's), I thought I would put together something that would calculate the vast majority of C(n, k) Lexicographic Index Numbers, where n is the total numbers drawn from, and k is the total numbers drawn.
I have created SIX Excel formulas to do this for 2, 3, 4, 5, 6 & 7 total numbers drawn (k numbers) in such a way that the formulas do NOT need to be amended or adjusted for the individual Lotto. Depending on which Lotto you play you just use the relevant formula below and copy it down as far as it is needed.
Setup the criteria for the k and n numbers.
We will use cell K2 for the total numbers drawn (k).
We will use cell K3 for the total numbers drawn from (n).
Setup the draws and formulas.
We will use cells B7:H7 and continuing down for the ACTUAL numbers drawn for each draw, so if you have 4 numbers drawn in your Lotto then cells B7:E7 and continuing down will only have numbers in them. If you have 6 numbers drawn in your Lotto then cells B7:G7 and continuing down will only have numbers in them etc.
In cell K7, COPY & PASTE the formula below that applies to your particular Lotto and copy it down as far as it is needed.
TWO NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-C7>0,COMBIN(K$3-C7,K$2-1),0)
THREE NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-D7>0,COMBIN(K$3-D7,K$2-2),0)
FOUR NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-E7>0,COMBIN(K$3-E7,K$2-3),0)
FIVE NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-(K$2-4)-E7>0,COMBIN(K$3-E7,K$2-3),0)-IF(K$3-F7>0,COMBIN(K$3-F7,K$2-4),0)
SIX NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-(K$2-4)-E7>0,COMBIN(K$3-E7,K$2-3),0)-IF(K$3-(K$2-5)-F7>0,COMBIN(K$3-F7,K$2-4),0)-IF(K$3-G7>0,COMBIN(K$3-G7,K$2-5),0)
SEVEN NUMBER LOTTO:
=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-(K$2-4)-E7>0,COMBIN(K$3-E7,K$2-3),0)-IF(K$3-(K$2-5)-F7>0,COMBIN(K$3-F7,K$2-4),0)-IF(K$3-(K$2-6)-G7>0,COMBIN(K$3-G7,K$2-5),0)-IF(K$3-H7>0,COMBIN(K$3-H7,K$2-6),0)
So just to summarise:
In cells B7:H7 and continuing down are the ACTUAL lotto numbers drawn.
In cell K2 is the total numbers drawn for your particular Lotto (k).
In cell K3 is the total numbers drawn from in your particular Lotto (n).
In cell K7 is the formula pertaining to your particular Lotto. Copy this formula down as far as it is needed.
You can format the cells with the Lexicographic Index Numbers to account for the thousands separator to make it easier to read.
That’s it!
Regards,
PAB
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.