lex order formula for excel

I

Irvin

Member
Hello :wavey:

I'm sure I wrote this yesterday but cant find the thread...might be going crazy :dizzy:

Does anyone have a formula to use on excel to calcluate the lexicographical (Don't know if spelt correctly) order of a set of numbers?

ie what is the lex order for number;

1,2,19,22,30,36 based on 6 balls from a pool of 40?

ie if 1,2,3,4,5,6 is 1 and 35,36,37,38,39,40 is 3,838,380.

Any help appreciated.

Thank you,

Irvin :D
 
Beaker

Beaker

Member
This is not mine but taken from another site.

For line [x1,x2,x3,...,xr] and assuming indexing starts at 1,

index = C(n,r) - C(n-x1, r) - C(n-x2, r-1) - C(n-x3, r-2) - ... - C(n-xr, 1)

where C(a,b) = 0 if a<b.

Should be able to use the COMBIN function and substitute the correct numbers. :agree:
 
I

Irvin

Member
Coudn't wait. :lol:

It works. Now just to make sense of it.

interesting range I have on 836 draws;

Lowest is 994 (1,2,3,5,18,40) and highest is 3,838,187 (31,32,33,35,36,38).

Thanks again Beaker
 
Beaker

Beaker

Member
Irvin said:
Coudn't wait. :lol:

It works. Now just to make sense of it.

interesting range I have on 836 draws;

Lowest is 994 (1,2,3,5,18,40) and highest is 3,838,187 (31,32,33,35,36,38).

Thanks again Beaker
Click to expand...
You had that ticket 31-32-33-35-36-38 come out in a draw??? :eek: :eek: WOW :dizzy:
 
I

Irvin

Member
Yeap

It is the only one in the NZ history where all 6 numbers are in one decade.

Don't know if anyone actually won it?
 
I

Irvin

Member
There were 38 first division winners for the 31,32,33,35,36,38 result.

Goes to show dont put all your numbers in one decade :) Too many people doing the same thing.
 
I

Irvin

Member
Andrew :wavey:

This is what the 38 winners would have roughly got in $US;

$19500.

Okay you have a point looks a reasonable amount just as long as you dont have a huge party before finding out how much you won :D.

You might have no money left after the party.

Just enough prize money to buy a decent 2litre car here. :lol: :lol:
 
J

jbiff

Member
lex in excel problems

Hey Irvin and everyone else,
Thanks for the info for lex order in excel.
I've been tryin to figure that one out for a while.
this is my first post ever...anywhere.
anyway the combin function works fine except when the 6th
number is max. I'm in illinois and play 6/52 and when 52 is
drawn all I get is #NUM!. I don't think I'm up to snuff to solve
this little dilemma. :confused:
this is what I'm using:
=COMBIN(52,6)-COMBIN(52-B2,6)-COMBIN(52-C2,5)-COMBIN(52-D2,4)-COMBIN(52-E2,3)-COMBIN(52-F2,2)-COMBIN(52-G2,1)

Can anybody tell me what it is I don't understand other than math.

thanx again
Jeff:confused:
 
Last edited:
I

Irvin

Member
Hello Jeff,

Heres what I did based on a 40 number pool. so max for ball 1 is 35 and max for ball 6 is 40.

=combin(40,6)-if(35-a1>0,combin(40-a1,6),0)-if(36-b1>0,combin(40-b1,5),0).........-if(40-f1>0,combin(40-f1,1),0)

Hope that helps.

The above works for me someone else might have it slightly different. But the above is how I understand it.

People here are very helpful and I hope I can pay it back as much as possible.


Regards,

Irvin:wavey:
 
P

PAB

Member
Lex Order Formula In Excel

Hi Jeff,

Here's what I did ( using the Formula above ) based on a 49 Number Draw. The Max for ball 1 is 44 and the Max for ball 6 is 49.

=COMBIN(49,6)-IF(44-A1>0,COMBIN(49-A1,6),0)-IF(45-B1>0,COMBIN(49-B1,5),0)-IF(46-C1>0,COMBIN(49-C1,4),0)-IF(47-D1>0,COMBIN(49-D1,3),0)-IF(48-E1>0,COMBIN(49-E1,2),0)-IF(49-F1>0,COMBIN(49-F1,1),0)

Just copy this Formula into Excel and replace the 49 with 40, the 48 with 39, the 47 with 38, the 46 with 37, the 45 with 36, the 44 with 35 and adjust the cell references.
Thanks to everyone who got us here in the end.

Good Luck for Tonight
PAB
 
Last edited:
P

PAB

Member
Lex Order Formula For Excel

Is there a similar Formula to get the Set Numbers from a Lexicographic Index Number ( whether the Numbers are in seperate cells or in one cell seperated with a hyphon )?.

Just a thought.

Regards
PAB
 
I

Irvin

Member
Hello Pab :wavey:

I dont know how to explain it but I use a number weight system to find a combination that matches a selected lex number.

So the position 1 ball number range would be higher.

Just like bianry the very left digit is the heavier number;

ie 11011

from the left is

16 + 8 + 0 + 2 + 1 = 27 I think thats right.

so use the formula you listed above and start with position 1.

Put your first number in position 1 if the lex number exceeds the one you want then reduce the position 1 number until it is under it then move on to position 2.

Sounds long winded but works for me.

But if someone does have an excel formula to work the lex number backwards great.

I can picture it just cant put it down in an excel formula....I gave up and went back to the weight thing.

:bawl:
 
P

PAB

Member
Hi Irvin,

Thanks for the reply.
I think the only managable solution would be an Excel Formula, so if anyone has come across one ( and feels in a generous mood ) would they kindly share it with us please.

Thanks in advance, and good luck tonight.
All the best.
PAB
:wavey:
 
G

GillesD

Member
Lex number in Excel

The following formula works fine for a 6/49 lottery:

=COMBIN(49,6)-IF(44-N1>0,COMBIN(49-N1,6),0)-IF(45-N2>0,COMBIN(49-N2,5),0)-IF(46-N3>0,COMBIN(49-N3,4),0)-IF(47-N4>0,COMBIN(49-N4,3),0)-IF(48-N5>0,COMBIN(49-N5,2),0)-IF(49-N6>0,COMBIN(49-N6,1),0)

where N1, N2 ... N6 are the respective numbers (in ascending order) you want the lex number. You can also replace these values by a reference to cells where they are in a sheet.
 
P

PAB

Member
Excel Formula for Lex Number to Number Set

Hi GillesD,

Thanks for the Formula.
What we actually want is a similar Formula to get the 6 Ball Set Numbers from a Lexicographic Index Number.
I know this Formula will probably be more Complex, but I think it would be interesting to have.
Once again Gilles, Thanks.

Best Regards
PAB
 
P

PAB

Member
Excel Formula for Ball Set Numbers

Hi Everyone,

Has anybody managed to work out an Excel Formula to get the 6 Ball Set Numbers from a Lexicographic Index Number yet.

Good luck to everyone tonight.

Regards
PAB
 
I

Irvin

Member
Pab:wavey:

I've thought about it and I think I can make the formula but it would be messy and pretty big.

It would be similar to the number weight system I mentioned earlier in this post.

I'll have a shot at putting it together tonight.

I presume your lotto system is based on 6/49.

Be warned though it will look messy and big.

It might sound crazy but someone might look at the formula that I put together and produce a better one later down the track.

The 6 numbers would have to be in ascending order from left to right and each in their own cell so 6 numbers would be in cells A1:F1.

For the weight system I think that would be easier.


Regards,

Irvin :wavey: :wavey:
 
You must log in or register to reply here.

Sidebar

Top