Combination FROM Lexicographix number
- Thread starter serge
- Start date
time*treat
Member
Since you're outputting to multiple cells, I think you'd need a macro rather than a formula.
Try this -- you'll have to adapt it to 6/49:
In a 5/44 lottery the set "03-17-24-35-42" would fall on line 317882
n1 = 3, n2 = 17, n3 = 24, n4 = 35, n5 = 42
n=44, r=5
x1 = nCr
x2 = (n-n1)Cr
x2 = (x1-x2)
x3 = (n-n2)C(r-1)
x4 = (n-n3)C(r-2)
x5 = (n-n4)C(r-3)
x6 = (n-n5)
p1 = (x2-(x3+x4+x5+x6)) = 317882
I also found a nifty online calculator that will convert from combo to line number and back again for any lottery:
http://www.runtobefree.com/default.aspx
Hope these help
In a 5/44 lottery the set "03-17-24-35-42" would fall on line 317882
n1 = 3, n2 = 17, n3 = 24, n4 = 35, n5 = 42
n=44, r=5
x1 = nCr
x2 = (n-n1)Cr
x2 = (x1-x2)
x3 = (n-n2)C(r-1)
x4 = (n-n3)C(r-2)
x5 = (n-n4)C(r-3)
x6 = (n-n5)
p1 = (x2-(x3+x4+x5+x6)) = 317882
I also found a nifty online calculator that will convert from combo to line number and back again for any lottery:
http://www.runtobefree.com/default.aspx
Hope these help
Frank
Member
Hi,
Because the values of the Lex numbers are a function of the six factors which total up to give the conjugate of the lexocological value, where conjugate is Combin(56,5) - the Lex value, a single formula for each number position is impractical if not impossible.
e.g
The Excel formula for Lexocological value for 5/56 where the 5 numbers are in cells C5 to G5 = =COMBIN(56,5)-IF(52-C5>0,COMBIN(56-C5,5),0)-IF(53-D5>0,COMBIN(56-D5,4),0)-IF(54-E5>0,COMBIN(56-E5,3),0)-IF(55-F5>0,COMBIN(56-F5,2),0)-IF(56-G5>0,COMBIN(56-G5,1),0)
This can be split into parts to understand how it works..
=COMBIN(56,5)
-IF(52-C5>0,COMBIN(56-C5,5),0)
-IF(53-D5>0,COMBIN(56-D5,4),0)
-IF(54-E5>0,COMBIN(56-E5,3),0)
-IF(55-F5>0,COMBIN(56-F5,2),0)
-IF(56-G5>0,COMBIN(56-G5,1),0)
each of those last 5 terms is a calculation of the number of possible combinations which can arise by virtue of a ball of a certain value in a certain column, taking into account the value of the ball to its left. Each of those IF statements calculates a figure which is a factor for a ball position.
Basically you add up all those (parts of numbers of combinations) together and then subtract that total from 3819816 which is combin(56,5) to get the Lexocological value.
To do the reverse involves factorising the given Lexocological value to find the highest possible factor for each ball position, given the inputted Lex value. This then points to the ball value in that position.
This opens up the possibility to use VLOOKUP to obtain the ball values but only after careful preparatory work has been done to create a table from the formula above, using all the ball values in all the possible sorted ascending positions.
Then the Vlookup function can be used to find the highest factor in each column (working left to right) extract the ball value , calculate the overspill (how much of the total Lex has not been used yet) and then use that to get the next factor to the right , and so on for each ball value in turn.
I have working spreadsheets which do this for 6/49 and for 5/56.
It is hard to explain how it works other than as described above without showing you the 5/56 spreadsheet as a demo and I don't know if I would be allowed to attach it here. I have a 1 byte limit on attachments !
I have a uploaded an (editable by permission only) version to Google docs which might help show what is involved. However I don't see a facility to post links here ??
Once the table has been set up on one sheet (I have called that sheet Demo) , then a whole row of working formulas on a different sheet (called "enter lex value and get numbers" ) can be copied down the page to give instant readout of a column of different ball values for different values of Lex input.
I hope this is useful , but I would need advice as to what I can upload or link to in order to take this method further.
Regards,
Frank
Because the values of the Lex numbers are a function of the six factors which total up to give the conjugate of the lexocological value, where conjugate is Combin(56,5) - the Lex value, a single formula for each number position is impractical if not impossible.
e.g
The Excel formula for Lexocological value for 5/56 where the 5 numbers are in cells C5 to G5 = =COMBIN(56,5)-IF(52-C5>0,COMBIN(56-C5,5),0)-IF(53-D5>0,COMBIN(56-D5,4),0)-IF(54-E5>0,COMBIN(56-E5,3),0)-IF(55-F5>0,COMBIN(56-F5,2),0)-IF(56-G5>0,COMBIN(56-G5,1),0)
This can be split into parts to understand how it works..
=COMBIN(56,5)
-IF(52-C5>0,COMBIN(56-C5,5),0)
-IF(53-D5>0,COMBIN(56-D5,4),0)
-IF(54-E5>0,COMBIN(56-E5,3),0)
-IF(55-F5>0,COMBIN(56-F5,2),0)
-IF(56-G5>0,COMBIN(56-G5,1),0)
each of those last 5 terms is a calculation of the number of possible combinations which can arise by virtue of a ball of a certain value in a certain column, taking into account the value of the ball to its left. Each of those IF statements calculates a figure which is a factor for a ball position.
Basically you add up all those (parts of numbers of combinations) together and then subtract that total from 3819816 which is combin(56,5) to get the Lexocological value.
To do the reverse involves factorising the given Lexocological value to find the highest possible factor for each ball position, given the inputted Lex value. This then points to the ball value in that position.
This opens up the possibility to use VLOOKUP to obtain the ball values but only after careful preparatory work has been done to create a table from the formula above, using all the ball values in all the possible sorted ascending positions.
Then the Vlookup function can be used to find the highest factor in each column (working left to right) extract the ball value , calculate the overspill (how much of the total Lex has not been used yet) and then use that to get the next factor to the right , and so on for each ball value in turn.
I have working spreadsheets which do this for 6/49 and for 5/56.
It is hard to explain how it works other than as described above without showing you the 5/56 spreadsheet as a demo and I don't know if I would be allowed to attach it here. I have a 1 byte limit on attachments !
I have a uploaded an (editable by permission only) version to Google docs which might help show what is involved. However I don't see a facility to post links here ??
Once the table has been set up on one sheet (I have called that sheet Demo) , then a whole row of working formulas on a different sheet (called "enter lex value and get numbers" ) can be copied down the page to give instant readout of a column of different ball values for different values of Lex input.
I hope this is useful , but I would need advice as to what I can upload or link to in order to take this method further.
Regards,
Frank
Frank
Member
Ok I'll post the link to the Google docs spreadsheet. It is located at ..
https://spreadsheets.google.com/ccc?key=0AtE9S-PhVVT9dEgtWEhuWmVPRkVYT2lRWEFMZGNMZnc&hl=en
You wont be able to enter any test Lex figures unless I authorise you, and I'd need your e-mail address to do that.
https://spreadsheets.google.com/ccc?key=0AtE9S-PhVVT9dEgtWEhuWmVPRkVYT2lRWEFMZGNMZnc&hl=en
You wont be able to enter any test Lex figures unless I authorise you, and I'd need your e-mail address to do that.
Frank
Member
Ok Serge, you can edit the sheet, which will allow you to type in some figures in the right places to see it working.
Demo sheet is dual function, top part allows you to enter 5 numbers (C5 to G5) and reads out the Lex in cell A5. You might want to generate a Lex here and then use that value (but typed in , not copied and pasted) on the other sheet to prove that it extracts the same numbers you used to generate it in the first place !
Lower part of Demo page contains the lookup table used on the other sheet, figures in grey are the factors found during the look up, and the ball numbers extracted are on the same row as that looked up factor.
The Demo sheet is not protected so I advise you not to type anything into anywhere other than cells C5 to G5.
On the second sheet, "Enter Lex value and get numbers" , you are advised not to type anything into anywhere except column A ! cols G to P are only visible for interest to show the calculations at the lookup stage, normally they would be hidden.
I hope you find it of use, the original Excel file is even better.
Frank
Demo sheet is dual function, top part allows you to enter 5 numbers (C5 to G5) and reads out the Lex in cell A5. You might want to generate a Lex here and then use that value (but typed in , not copied and pasted) on the other sheet to prove that it extracts the same numbers you used to generate it in the first place !
Lower part of Demo page contains the lookup table used on the other sheet, figures in grey are the factors found during the look up, and the ball numbers extracted are on the same row as that looked up factor.
The Demo sheet is not protected so I advise you not to type anything into anywhere other than cells C5 to G5.
On the second sheet, "Enter Lex value and get numbers" , you are advised not to type anything into anywhere except column A ! cols G to P are only visible for interest to show the calculations at the lookup stage, normally they would be hidden.
I hope you find it of use, the original Excel file is even better.
Frank
